Insert into with output clause

Question

INSERT INTO Table1(group, account)

OUTPUT inserted.Id, B.title, B.amount 
INTO Table2(id2, title, amount) 

SELECT A.*,
         B.title,
           B.amount,
            B.id2    
FROM Table1 AS A

LEFT OUTER JOIN
(SELECT  title,
          amount,
           id2
 FROM Table2) AS B
 ON A.id = B.id2

i'm stuck with this..i have two join tables and what i want is to copy the same set of data from table1 to itself and copy the new id of the newly copied data from table1 to table2 column id2 by using OUTPUT clause.

but now with the query above i cant get through the column that i needed..how can i insert column B.title & B.amount to table2 ?

Answer 1

If table 1 and table 2 have a 1:1 relationship, and no foreign key exists between the two then you could do this in a single statement:

MERGE Table1 AS a 
USING
(   SELECT  A.[group], A.account, B.title, B.amount, B.id2    
    FROM    Table1 AS A
            LEFT OUTER JOIN Table2 AS B
                ON A.id = B.id2
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, B.title, B.amount 
    INTO Table2(id2, title, amount);

Example on SQL Fiddle

Realistically though, if your tables are related they should have a foreign key, and in most cases they won't be 1:1, rather 1:n.

In which case you would still need to use MERGE to caputre both the new ID and the old ID, but you would then need to capture this mapping in a temporary table before performing a second insert to Table2:

DECLARE @Map TABLE (OldID INT NOT NULL, NewID INT NOT NULL);

MERGE Table1 AS a 
USING
(   SELECT  A.ID, A.[group], A.account  
    FROM    Table1 AS A
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, b.ID
    INTO @Map(NewID, OldID);

INSERT Table2 (id2, title, amount)
SELECT  m.NewID, b.title, b.amount
FROM    @Map AS m
        INNER JOIN Table2 AS b
            ON b.ID2 = m.OldID;

Example on SQL Fiddle