Creating a mapping table using the T-SQL MERGE statement

When you need to insert data from 2 staging tables in 2 production tables, while maintaining a foreign key relationship between the 2 tables, there are several options. Most suggest to insert the IDENTITY column from the staging tables in the production tables using SET IDENTITY_INSERT ON, but I decided to take a safer approach using a mapping table between the identity value of the staging and the production tables.  To create this mapping table I used the OUTPUT clause of the MERGE statement, as shown in the (simplified) example below:

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 the 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

Don’t forget to clean up the staging table afterwards…

Note that we need to use the MERGE statement for this technique, since the regular INSERT statement doesn’t allow us to reference the source table, but only the ‘inserted’ temporary table.

As always, I’m curious for your opinion on this technique, so feel free to drop a line in the comments…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s