SQL Server insert statement using INSERTED to get new ID and existing ID

Question

Hello SO,

I'm inserting records from one sql server db into another. I need to get the newly inserted id and update a field on the source table. I can get the new ID with INSERTED option no problem. However, I cannot seem to get the ID from the source table. The destination table does not have a field for the source's ID. The source table is a conversion table and I do not want to pollute the destination table with conversion fields. This may not be possible but I thought I'd first check with you guys.

drop table #Table1
CREATE TABLE #Table1
(
    Table1ID INT,
    Table2ID INT,
    NAME VARCHAR(32)
)

INSERT INTO #Table1
VALUES 
      (1, NULL, 'Fred')
     ,(2, NULL, 'Tom')
     ,(3, NULL, 'Sally')     

--ok, im inserting into #Table2
drop table #Table2
CREATE TABLE #Table2
(
    [Table2ID] [int] IDENTITY(1,1) NOT NULL,
    NAME VARCHAR(32)
)

--THE RUB, I want to insert Table2ID into table3 
--along with Table1ID. I cannot seem to reference table1
--Any Ideas?
insert into #Table2(NAME)
OUTPUT INSERTED.Table2ID, T.Table1ID into #Table3
select Name from #Table1 T

Answer 1

Put the conversion id field in the table. This is not polluting the table, it is handling the problem properly. Plus you can then get it back out in the output clause. And it will make it simpler to update child tables as well. I see no reason at all not to do this unless you have poor developers who use Select * in production code.

Answer 2

You cannot do it with INSERT (sine it lacks a FROM clause of its own) but you can do it with MERGE:

MERGE INTO #Table2 as t2
USING #Table1 as t1 ON 0=1
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (NAME) VALUES (t1.NAME)
OUTPUT INSERTED.Table2ID, t1.Table1ID ;