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

Let’s 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  
)

Let’s 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 the nature of the INSERT statement. The SELECT statement is considered to be apart from the INSERT statement, so you can’t reference its tables in the OUTPUT clause. However, you can achieve the desired outcome using a MERGE SQL statement, which is a part of the SQL standard from 2003, but is 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: