Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 421
Steve Avatar asked Jul 02 '10 16:07

Steve


2 Answers

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 ;
like image 135
Remus Rusanu Avatar answered Sep 24 '22 06:09

Remus Rusanu


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.

like image 33
HLGEM Avatar answered Sep 22 '22 06:09

HLGEM