Migrating Data From Old Schema to New 2

[Gooser]

Okay, I've mulled this over for quite some time and I'm just not sure how to solve this one. I have a TON of legacy data that needs to be forced into our new schema. I can figure out how to do this on a record-by-record basis, but how do I do this for all of my records at once?

I am rather confused at how to approach this strategically. Do I start with the lookup tables and work my way back to the base tables? How do I account for changes in schema? How do I do both at the same time. Any ideas or guidance would be greatly appreciated at this point. I'm kind of at a loss for now.

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Bailout We Need

 

[snitin78]

Is buying a tool an option for you? If yes, then I would suggest you have a look at the tools provided by RedGate. They are a little expensive but can come in really handy at times...the DBA at my last job just loved the Toolbelt.

Here is one for your specific need:

http://www.red-gate.com/products/SQL_Data_Compare/index.htm

Nitin

 

[Gooser]

Not really. I have a pretty decent understanding about how the old data fits into the new schema, plus I have support of the desiger of the old system if I need it. However, it is a bit of a daunting task and I'm really looking for strategy guidance if anybody has any...please.

v/r
Gooser

 

[gmmastros]

I can figure out how to do this on a record-by-record basis, but how do I do this for all of my records at once?

How would you do this on a record-by-record basis?

Ordinarily, you need to start with the lookup tables because there SHOULD be foreign key relationships between the lookup tables and the data tables.

For example... suppose you have an employee table with a DepartmentId column. You should not be able to insert a row with a DepartmentId that does not exist in the Department table.

If this were my project, I would make sure both databases are connected to the same instance. Then, I would start writing queries to copy data from one database to another. Again... starting at the lookup tables. so....

Insert 
Into   newDatabase.dbo.Department(DepartmentId, DepartmentName)
Select DepartmentId, DepartmentName
From   OldDatabase.dbo.Department

If you have identity column in your new schema, you will need to set identity insert on before you can copy the identity value from one table to another.

for example:

Set Identity_Insert NewDatabase.dbo.Department On

Insert 
Into   newDatabase.dbo.Department(DepartmentId, DepartmentName)
Select DepartmentId, DepartmentName
From   OldDatabase.dbo.Department

Set Identity_Insert NewDatabase.dbo.Department Off

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

How about using DTS or SSIS?

 

[Gooser]

George--

By the record-by-record basis, I meant that I know how the old schema's data maps to the new schema, not that I would consider copying them over one by one.

Why would it be necessary to have them on the same instance?

What you said about starting with the lookups is what I was thinking had to be done. I guess I'll just have to hash out how all this is going to work.

Thanks for the terrific insight, as usual.

v/r
Gooser

 

[gmmastros]

I didn't say it was necessary to have them on the same instance. What I meant was that it would be helpful to have them on the same instance. You see, if it's on the same instance, you can simply query from database to database. This will give you the best performance, too.

If the databases are not on the same instance, you can still use this method, but you'll want to set up a linked server to query from one DB to another. This is slightly more difficult to use and to set up.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom