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

Question

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.

score 3 · Accepted Answer · answered Oct 19 '10 at 20:33

3

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.

answered Oct 19 '10 at 20:33

Jeff Ogata

56,645
19
114
127

1

when you create the identity on the mapping table, set the seed so that it doesn't conflict with existing data. – Jeff Ogata Oct 19 '10 at 20:51
1

[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 Oct 19 '10 at 20:57
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 Oct 19 '10 at 21:12

score 2 · Answer 2 · edited Mar 25 '13 at 16:46

I created a mapping table based on the OUTPUT clause of the MERGE statement. No IDENTITY_INSERT required.

In the example below, there is RecordImportQueue and RecordDataImportQueue, and RecordDataImportQueue.RecordID is a FK to RecordImportQueue.RecordID. The data in these staging tables needs to go to Record and RecordData, and FK must be preserved.

RecordImportQueue to Record is done using a MERGE statement, producing a mapping table from its OUTPUT, and RecordDataImportQueue goes to RecordData using an INSERT from a SELECT of the source table joined to the mapping table.

DECLARE @MappingTable table ([NewRecordID] [bigint],[OldRecordID] [bigint])

MERGE [dbo].[Record] AS target
USING (SELECT [InstanceID]
            ,RecordID AS RecordID_Original
            ,[Status]
        FROM [RecordImportQueue]
        ) AS source
ON (target.RecordID = NULL) -- can never match as RecordID is IDENTITY NOT NULL.
WHEN NOT MATCHED THEN
    INSERT ([InstanceID],[Status])
    VALUES (source.[InstanceID],source.[Status])
    OUTPUT inserted.RecordID, source.RecordID_Original INTO @MappingTable;

After that, you can insert the records in a referencing table as folows:

INSERT INTO [dbo].[RecordData]
    ([InstanceID]
    ,[RecordID]
    ,[Status])
SELECT [InstanceID]
    ,mt.NewRecordID -- the new RecordID from the mappingtable
    ,[Status]
FROM [dbo].[RecordDataImportQueue] AS rdiq
JOIN @MappingTable AS mt
ON rdiq.RecordID = mt.OldRecordID

Although long after the original post, I hope this can help other people, and I'm curious for any feedback.

score 1 · Answer 3 · answered Oct 19 '10 at 19:56

1

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.

answered Oct 19 '10 at 19:56

Joe Stefanelli

132,803
19
237
235

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 Oct 19 '10 at 20:20
@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 Oct 19 '10 at 20:57
Couldn't you encounter clashing identity values when inserting into the target table? – Daniel Oct 19 '10 at 21:02
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 Oct 19 '10 at 21:03
@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 Oct 19 '10 at 21:04

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

3 Answers3