Exporting from an old DB structure to new DB structure

Question

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?

wRAR · Accepted Answer · 2010-03-01 15:19:20Z

2

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

answered Mar 1, 2010 at 15:19

wRAR

25.7k5 gold badges89 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Neil Knight Over a year ago

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)

wRAR Over a year ago

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.

Gabriele Petrioli Over a year ago

@Ardman, read 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.

p.marino Over a year ago

See sqlteam.com/article/…

Collectives™ on Stack Overflow

Exporting from an old DB structure to new DB structure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related