Match identity columns after INSERT

One common requirement in SQL when inserting data between tables is to match the new identity column with the old identity column. The most common solution to this problem is to perform a cursor and do the inserts one at a time. While this may work, it is not very efficient because set based operations are the bread and butter of SQL. Anytime looping comes into play, excessive resource consumption also occurs.

There are a couple solutions to this, both use the OUTPUT clause introduced in SQL 2005. The first solution uses an intermediary reference table to hold the old and new values. This is done by outputting the inserted identity columns while sorting the source identity columns. You then join back on the source identity columns to get both old and newly created identities. This solution was created by my good SQL developer friend Mike Newell.

Method 1

[cc lang=”sql”]
— Drop temp tables if they already exist
IF OBJECT_ID(‘TempDB..#source’, ‘U’) IS NOT NULL DROP TABLE #source;
IF OBJECT_ID(‘TempDB..#target’, ‘U’) IS NOT NULL DROP TABLE #target;
IF OBJECT_ID(‘TempDB..#xref’, ‘U’) IS NOT NULL DROP TABLE #xref;
GO

— Create the temp tables
CREATE TABLE #source (id INT PRIMARY KEY IDENTITY(1, 1), data INT);
CREATE TABLE #target (id INT PRIMARY KEY IDENTITY(1, 1), data INT);
CREATE TABLE #xref (row INT PRIMARY KEY IDENTITY(1, 1), source_id INT, target_id INT);
GO

— If xref table is being reused, make sure to clear data and reseed by truncating.
TRUNCATE TABLE #xref;

— Fill source table with dummy data (even numbers)
INSERT INTO #source (data)
SELECT 2 UNION SELECT 4 UNION SELECT 6 UNION SELECT 8;
GO

— Fill target table with dummy data (odd numbers) to simulate existing records
INSERT INTO #target (data)
SELECT 1 UNION SELECT 3 UNION SELECT 5;
GO

— Insert source table data into target table. IMPORTANT: Inserted data must be sorted by the source table’s primary key.
INSERT INTO #target (data)
OUTPUT INSERTED.id INTO #xref (target_id)
SELECT data
FROM #source
ORDER BY id ASC;
GO

— Update the xref table with the PK of the source table. This technique is used for data not captured during the insert.
;WITH src AS (SELECT id, row = ROW_NUMBER() OVER (ORDER BY id ASC) FROM #source)
UPDATE x
SET x.source_id = src.id
FROM #xref AS x
JOIN src ON src.row = x.row;
GO

— Verify data
SELECT * FROM #source;
SELECT * FROM #target;
SELECT * FROM #xref;
GO

— Clean-up
IF OBJECT_ID(‘TempDB..#source’, ‘U’) IS NOT NULL DROP TABLE #source;
IF OBJECT_ID(‘TempDB..#target’, ‘U’) IS NOT NULL DROP TABLE #target;
IF OBJECT_ID(‘TempDB..#xref’, ‘U’) IS NOT NULL DROP TABLE #xref;
GO
[/cc]

I will say it’s unfortunate that as of SQL Server 2008 R2, the output clause does not allow the outputting of both old and new ID’s into the same row. There is another option to this using the MERGE statement also, which only works in SQL Server 2008.