We are upgrading a database that is on a SQL 2000 server up to SQL 2005. The database name on the servers and the table names will be the same, however a field or two in many of the tables will be expanding by a couple of characters to make room for expanded order numbers which is why I cannot just do a db migration wizard. (BTW, the order numbers are stored as text for compatibility with some other system that I cannot change)
I used the wizard in 2000 to generate a SQL script to create the new tables for 2005. I altered the script to make the tables on the new server with the expanded sizes so all I need now is to create the script to copy the data and insert the extra characters into the fields in question.
The field that is changing will be the OrderNum fields which are stored as text and has a range of 200000 to 299999 for Sales, 700000 to 799999 for POs and other starting numbers depending on the series. This field is going to increase to 8 characters so any order number now that is 2xxxxx is going to be changed to 200xxxxx. For example 212345 is going to become 20012345.
I can create select queries easy enough to make the alteration:
Select Left([OrderNum],1) & "00" & Right([OrderNum],5), LineNum, Item from MyTable
This gives me the data that am going to need to import, but I am not familiar enough with SQL scripts to write something that I can run on the SQL 2005 server to suck this info in from the SQL 2000 server into the new table.
I was hoping I could run through a wizard and save it as a script to gut out the pieces that I need, but I can't seem to fine what I need.
I used the wizard in 2000 to generate a SQL script to create the new tables for 2005. I altered the script to make the tables on the new server with the expanded sizes so all I need now is to create the script to copy the data and insert the extra characters into the fields in question.
The field that is changing will be the OrderNum fields which are stored as text and has a range of 200000 to 299999 for Sales, 700000 to 799999 for POs and other starting numbers depending on the series. This field is going to increase to 8 characters so any order number now that is 2xxxxx is going to be changed to 200xxxxx. For example 212345 is going to become 20012345.
I can create select queries easy enough to make the alteration:
Select Left([OrderNum],1) & "00" & Right([OrderNum],5), LineNum, Item from MyTable
This gives me the data that am going to need to import, but I am not familiar enough with SQL scripts to write something that I can run on the SQL 2005 server to suck this info in from the SQL 2000 server into the new table.
I was hoping I could run through a wizard and save it as a script to gut out the pieces that I need, but I can't seem to fine what I need.