Exporting from an old DB structure to new DB structure

views:

answers:

Exporting from an old DB structure to new DB structure

I have inherited a database that has no real referential integrity at all, so I've created a new database with this corrected. The problem I have now, is that I have large amounts of data (around 50million rows) with previously assigned IDs. I need to keep these IDs in my new Database else I'll lose a lot of references from the data. So, initially I thought of turning off the Identity on the columns, and then insert the data and switch it on.

Does this sound like a feasible plan? Are there any flaws to this? Has anyone tried and tested it?

+2 A:

SET IDENTITY_INSERT ON is just for these cases. Don't forget to adjust identity counters after insert.

wRAR 2010-03-01 15:19:20

So, by specifying IDENTITY_INSERT ON, this will keep my existing IDs from my old DB? This is kind of the crucial part of moving the data. Sorry for the idiocy, but I need to understand fully before I try and implement it :o)

Ardman 2010-03-01 15:24:30

If your insert query contains id column and values for it from the old DB, yes. This option allows inserting any values into identity columns as if they were ordinary columns.

wRAR 2010-03-01 15:28:00

@Ardman, read http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx for details. A small quote `Allows explicit values to be inserted into the identity column of a table. `

Gaby 2010-03-01 15:28:39

Perfect thanks! :o)

Ardman 2010-03-01 15:29:54

See http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

p.marino 2010-03-01 15:47:38

ansaurus

tags:

views:

answers:

Exporting from an old DB structure to new DB structure

related questions