Best way to move data between tables and generate mapping of old to new identity values

views:

answers:

+2 Q:

Best way to move data between tables and generate mapping of old to new identity values

I need to merge data from 2 tables into a third (all having the same schema) and generate a mapping of old identity values to new ones. The obvious approach is to loop through the source tables using a cursor, inserting the old and new identity values along the way. Is there a better (possibly set-oriented) way to do this?

UPDATE: One additional bit of info: the destination table already has data.

+1 A:

I think I would temporarily add an extra column to the new table to hold the old ID. Once your inserts are complete, you can extract the mapping into another table and drop the column.

Joe Stefanelli 2010-10-19 19:56:17

This assumes I can alter the schema of the target table, which I can, but I'm curious how this could be solved if that isn't possible.

Daniel 2010-10-19 20:20:46

@Daniel: Essentially, I'd use the same technique, but instead of altering the target table, I'd create a temp table that would consist of the target table's schema (including the identity column) plus the additional old ID column. Insert into the temp table, then use `SET IDENTITY_INSERT NewTable ON` and insert from the temp table to your new table. Remember to `SET IDENTITY_INSERT NewTable OFF` when you're done.

Joe Stefanelli 2010-10-19 20:57:10

Couldn't you encounter clashing identity values when inserting into the target table?

Daniel 2010-10-19 21:02:24

Just noticed that my previous comment is basically the same as [adrift's answer](http://stackoverflow.com/questions/3971830/best-way-to-move-data-between-tables-and-generate-mapping-of-old-to-new-identity/3972529#3972529)

Joe Stefanelli 2010-10-19 21:03:29

@Daniel: As mentioned in [adrift's answer](http://stackoverflow.com/questions/3971830/best-way-to-move-data-between-tables-and-generate-mapping-of-old-to-new-identity/3972529#3972529), set the initial seed of the identity in the temp table high enough to avoid existing values in your target table and you should be OK.

Joe Stefanelli 2010-10-19 21:04:34

+3 A:

Create your mapping table with an IDENTITY column for the new ID. Insert from your source tables into this table, creating your mapping.

SET IDENTITY_INSERT ON for your target table.

Insert into the target table from your source tables joined to the mapping table, then SET IDENTITY_INSERT OFF.

adrift 2010-10-19 20:33:59

How would this work if the target table already has data?

Daniel 2010-10-19 20:45:30

when you create the identity on the mapping table, set the seed so that it doesn't conflict with existing data.

adrift 2010-10-19 20:51:19

[This MSDN article](http://msdn.microsoft.com/en-us/library/ms175098.aspx) states: `Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.` Is there a way to reliably avoid clashing identity values?

Daniel 2010-10-19 20:57:01

This seems a bit ugly, but maybe it's the best that can be done. I _really_ wish this was possible using the OUTPUT clause.

Daniel 2010-10-19 21:12:14

ansaurus

tags:

views:

answers:

Best way to move data between tables and generate mapping of old to new identity values

related questions