Moving to new schema and from 2000 to 2008.

Question

Moving to new schema and from 2000 to 2008.

goody71

Grasshopper

Points: 10
More actions
July 17, 2012 at 12:11 pm

#264005

I'm moving our DB from 2000 to 2008.
I'm also developing a .NET App and plan to use this 'starting over' point to fix the schema of our DB.
For example our 2000 'member' table had 100 columns. I've been working on Normalizing the data but now realized I don't know the best way to import to the new schema.
I'm planning to use this schema for the new 'member' tables:
http://www.databaseanswers.org/data_models/customers_and_addresses/index.htm
The old schema had everything(name, addresses, email, phone #'s, and other demographic info) in one table.
What I realized after Normalizing is that I'm not sure how to import all the data into the new schema while keeping it all constrained to the correct member.
Do I bring in my old primary_keys to keep everything together?
Is there some magic app or way in Visual Studio to pull this off without me going crazy?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

goody71 (7/17/2012)
I'm moving our DB from 2000 to 2008.
I'm also developing a .NET App and plan to use this 'starting over' point to fix the schema of our DB.
For example our 2000 'member' table had 100 columns. I've been working on Normalizing the data but now realized I don't know the best way to import to the new schema.
I'm planning to use this schema for the new 'member' tables:
http://www.databaseanswers.org/data_models/customers_and_addresses/index.htm
The old schema had everything(name, addresses, email, phone #'s, and other demographic info) in one table.
What I realized after Normalizing is that I'm not sure how to import all the data into the new schema while keeping it all constrained to the correct member.
Do I bring in my old primary_keys to keep everything together?
Is there some magic app or way in Visual Studio to pull this off without me going crazy?

There is no magic app, unless you consider SSIS or some other ETL tool like Informatica magical, which I do not.

For this effort I would recommend either SSIS or T-SQL depending on what you're most comfortable with in terms of development. Either one will do however neither are magic, i.e. any chosen method will require some analysis effort to map old tables and columns to new tables and columns as well as developing a strategy to maintain referential integrity in the process. Maintaining referential integrity does not necessarily mean you must bring your old keys into the new database. In the process of migrating data into the new schema you can allow the new schema to issue new key values (i.e. identity or sequence values) as long as you capture them as their created in the parent tables and you propagate those values properly throughout the rest of the data model as you migrate the child data into the new schema.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Chrissy321 SSCoach Points: 15763 More actions · Answer 2

If you are using identity fields as keys in your new schema you may want to explore the

SET IDENTITY_INSERT statement so you can insert explicit values.

http://msdn.microsoft.com/en-us/library/ms188059.aspx

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

i would execute in the following fashion

1. move all the table definitions into new schema

2. copy data over new schema (explorw bcp option)

3. create FKs and Indexes

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

goody71 Grasshopper Points: 10 More actions · Answer 4

Chrissy321 (7/17/2012)

If you are using identity fields as keys in your new schema you may want to explore the

SET IDENTITY_INSERT statement so you can insert explicit values.

http://msdn.microsoft.com/en-us/library/ms188059.aspx%5B/quote%5D

I was wondering if there was a way to do that. Thank you for the link!

A thank you all for the feedback/advice. This is great.

Also, in today's SQL Server Central email Step 6 of an Integration Services piece lead me to the other steps:

http://www.sqlservercentral.com/stairway/72494/

Thanks again, all. What a great resource!