Importing data from SQL 2000 to 2005 and changing a field

[JordanCN2]

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.

 

[drlex]

Are there any reasons (triggers etc) why you can't copy across all the data and then just do an update on the order fields?

The update also sounds like a good opportunity to use STUFF

e.g. SELECT STUFF('212345',2,0,'00') results in 20012345.

soi là, soi carré

 

[JordanCN2]

Yes, on a few tables there is a ton of junk that I need to leave behind and that would be filtered out with the query on the transfer. There are hundreds of GB in these tables and about 10% is junk data. This is why I cannot do a simple backup, copy, restore and STUFF.

The original programmer would allow user to enter info that was not a real order number (did not check) so we have a bunch of stuff that you cannot associate with an order so it is useless. The new program corrects this so data can only be associated with real orders and the items on the order

 

[simian336]

There is not a wizard that will let you pick and choose what you want from one table to anouther.

You probably need to backup the old database then restore it to the new server. Then you can use SELECT INTO to copy records from your old DB to the newone. And use UPDATE to make the changes to your OrderNumber.

Simi

 

[JordanCN2]

How about this ...

1. I ran the wizard to extract the structure of the DB into a SQL script (Tables, Views, Users)

2. I altered the SQL to change the fields that needed to increase. So now I have the structure I want ready to import.

3. I go to the SQL 2005 server and run this script which creates the DB and the new structure.

4. I create Views on the old server with the fields that I want to change set as:

Select Left([OrderNum],1) + '00' + Right([OrderNum],5) as OrderNum, blah, blah, blah.

5 Run the Import wizard to import the data, but map my view in the old DB to the new table in the new DB.

6. Save this as a package as well.

The only thing I do not know is if the Import wizard will try to re-create the tables I have in the destination datbase. Do you know if it will recreate or just import the data into the structure I have setup?

 

[djj55]

simian336, You can "Write a query to specify the data to transfer" in the import/export wizard.

JordanCN2, Can you link the servers?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[JordanCN2]

That is kind of the part I am having difficulty with. It was easy to create the SQL to create all the tables, but I would really like to be able to have a script that also:

- Connects to the old SQL2000 server
- Run a copy query like this for each table:

INSERT INTO [My Orders] ( OrderNum, ItemNum, Qty )
SELECT [My Orders].OrderNum, [My Orders].ItemNum, [MyOrders].Qty
FROM [My Orders];

So I need something that will connect to the SQL 2000 server and also the syntax for how I should distinguish between the [My Orders] table on each server's DB.

 

[simian336]

If you restore a backup to the new server you won't need to connect to the old server. You can give it a different name if you need to.

Then you can query it all day long.

Simi

 

[JordanCN2]

The DB is too big to restore to the new server and copy. It has about 10 years worth of data in it and a lot of that needs to be transformed and filtered out which is why I want to use the queries to correct and filter the data.

I have been doing some Googling and found some leads on what appears to be what I want to do. I believe I what I am looking to do is "link" the servers and setup remote logons.

I have seen store producures on the servers and documentation for:

sp_addlinkedserver
sp_addlinkedsrvlogin
sp_addremotelogin

I have been trying to read my way through setting up a connection, but just can't get the order or parameters correct.

It appears that once I setup this connection correctly I can do something like the following:

Insert into [NewServer].[Mydb].[dbo].[mytable] (........)
Select .......
From [OldServer].[Mydb].[dbo].[mytable]
Where ......

I just need to get this connection setup.

 

[gmmastros]

The DB is too big to restore to the new server

I believe I what I am looking to do is "link" the servers and setup remote logons.

I assume that the database must be huge because hard drive storage space is very cheap these days. Linked servers are nice, but they have performance problems when you are dealing with a lot of data. Now, please don't misunderstand me. The databases can be huge and you can still get good performance. However, if you need to transfer huge amounts of data, things can slow to a crawl.

The easiest way to set up a linked server is to use the GUI tools. Open SQL Server Management Studio, Expand "Server Objects", right click on "Linked Servers", and click on "New Linked Server". Fill in the appropriate information and you're done.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JordanCN2]

Finally I have success.....

I was getting errors on the linked tables because I was trying to connect to the SQL 2000 SP4 x32 database from the SQL 2005 x64. MS has an article about how a sql script needs to be run on the 2004 server after installing SP3 or 4 in order to connect with a x64 system.

http://support.microsoft.com/kb/906954

Ran the script and BOOM, my sql statements ran great.