How to get both inserted source table id and target table id?

Lets say we have two tables source and target. We want to copy records from the source to the target table and save mappings between old and new ids in a third table.

DECLARE @source TABLE
(
   id INT IDENTITY(5,1) NOT NULL PRIMARY KEY,
   name VARCHAR(max)  
)

DECLARE @target TABLE
(
   id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   name VARCHAR(max)
)

DECLARE @inserted TABLE
(
   id INT NOT NULL,
   clone_id INT NOT NULL  
)

Lets populate the source table:

INSERT INTO @subscription
(name)
VALUES
('Test 1'),
('Test 2'),
('Test 3')

Now we can populate the target table with the following code:

INSERT INTO @target
        (name )
SELECT name + ' - Cloned' FROM @source

You can try to populate inserted table using the following code:

INSERT INTO @target
        (name )
OUTPUT s.id, INSERTED.id INTO @inserted
SELECT name + ' - Cloned' FROM @source s

But you will get a syntax error:

The multi-part identifier "s.id" could not be bound.

This is due to a nature of the INSERT statement. The SELECT statement considered to be apart from the INSERT statement, so you can't reference its tables in the OUTPUT clause. However you can achieve desired outcome using MERGE sql statement, which is a part of SQL standard from 2003, but rarely used by anybody.

MERGE @target t
USING @source s
ON 0=1
WHEN NOT MATCHED THEN 
INSERT (name) VALUES (s.name + ' - Cloned')
OUTPUT s.id, INSERTED.id INTO @inserted;

SELECT * FROM @source
SELECT * FROM @target
SELECT * FROM @inserted

Results:

idname
5Test 1
6Test 2
7Test 3

idname
1Test 1 - Cloned
2Test 2 - Cloned
3Test 3 - Cloned

idclone_id
51
62
73

Posted on Saturday, January 5, 2013 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5