SQL Server INSERT-OUTPUT including column from other table

Question

I need to insert data into a table and I need to output the newly inserted ids from the destination table mapped alongside the Ids from the source table. I have the following query:

DECLARE @mapping TABLE(
            DestId int,
            SourceId int);


INSERT INTO dest_table (column1, column2)
OUTPUT INSERTED.Id as DestId, src_table.Id as SourceId   INTO @mapping 
SELECT 
    src_table.column1,
    src_table.column2

FROM src_table

Which gives the following error:

The multi-part identifier "src_table.Id" could not be bound.

Normally, I would use a MERGE statement. However in this case it has to run on SQL Server 2005!!!

Is there any way of achieving this without resolving to a cursor and inserting values one by one?

You can't access the columns not part of the insert, unless you're using MERGE which i don't think is available in SQL Server 2005, you have to join them back by using some way of identify stuff, or use some hacks like re-purposing columns for IDs — siggemannen
– siggemannen, Commented Jul 30, 2023 at 18:17

user212514 · Accepted Answer · 2019-12-13 23:33:23Z

0

It looks like a typo. The table is src_table1 (with the 1 at the end) whereas the aliasing is using src_table (without the 1 at the end). Can you just add the alias to the table?

DECLARE @mapping TABLE(
            DestId int,
            SourceId int);


INSERT INTO dest_table (column1, column2)
OUTPUT INSERTED.Id as DestId, src_table.Id as SourceId   INTO @mapping 
SELECT 
    src_table.column1,
    src_table.column2

FROM src_table1 src_table

answered Dec 13, 2019 at 23:33

user212514

3,1381 gold badge17 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JustinHui Over a year ago

Ooops, thanks for pointing that out! The typo was only in my question though! The table is actually meant to be src_table. It is still an issue with the corrected table name. I have edited my question.

MNB · Accepted Answer · 2025-10-21 15:07:03Z

I know this is an old question, but you can abuse a MERGE statement to allow the referencing of the source table in the OUTPUT clause

DECLARE @mapping TABLE (
    DestId int,
    SourceId int
);

MERGE INTO dest_table AS T 
USING src_table1 AS S ON 1=0 -- false predicate will ALWAYS not match!
WHEN NOT MATCHED THEN 
    INSERT (column1, column2) 
    VALUES (S.column1, S.column2) 
    OUTPUT inserted.id as DestID, S.id AS SourceID INTO @mapping
;

the trick is the 1=0 in the merge predicate forcing it to always use the INSERT WHEN NOT MATCHED clause of the MERGE.

Edited to add: In response to the downvote I have since realised the question explicitly discounts the use of MERGE due to SQL-2005 being in use, however I thought the trick outlined in the answer above to be interesting enough to record here. If the community feels further downvotes are warranted I will delete my answer.

Collectives™ on Stack Overflow

SQL Server INSERT-OUTPUT including column from other table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related