How to update a column based on inserted ids when using insert select?

I need to update a column in the one table with the ids generated after an insert into another table. This is what I have:

    tbl1 (id, tbl2id, col1, col2)
    tbl2 (id, col1, col2)

   开发者_StackOverflow社区 insert into tbl2
    select col1, col2
    from tbl1
    where tbl1.tbl2id is null

At this point I need to populate tbl1.tbl2id with the id for all the rows that were inserted by the above insert statement, so that if the above insert statement is run again, I will not have duplicates in tbl2. I'm on SQL Server 2005.

---

Use the OUTPUT clause and a Temp Table.

OUTPUT clause info is here: http://msdn.microsoft.com/en-us/library/ms177564.aspx

It should look like:

CREATE TABLE #NewIDs (Tbl1Col1 INT, Tbl2ID INT)

insert into tbl2 (col1, col2)
OUTPUT inserted.col1, inserted.id INTO #NewIDs (Tbl1Col1, Tbl2ID)
  select col1, col2  
  from tbl1  
  where tbl1.tbl2id is null

UPDATE t
SET    t.tbl2id = tmp.Tbl2ID
FROM   Tbl1 t
INNER JOIN   #NewIDs tmp
        ON   tmp.Tbl1Col1 = t.Col1

The real trick is making sure that one of the fields that you are inserting into Table2 from Table2 can be used to JOIN on so hopefully it is unique, or unique enough. The only real complication is that the ID for Table 1 is not part of the query doing the INSERT so it is not available to the OUTPUT clause.

---

Using a trigger you could do

CREATE TRIGGER trigger_name 
ON tbl2
AFTER INSERT
AS 
     INSERT tbl1 (tbl2id, col1, col2)
     SELECT id, col1, col2 
     FROM inserted