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 ?

Possible Duplicate - [Is it possible to for SQL Output clause to return a column not being inserted?](https://stackoverflow.com/q/10949730/1048425) — GarethD, Jul 19 '18 at 09:17
That query won't run anyway; `GROUP` is a reserved word in SQL Server. If you have a column called `group` you have to quote it: `[group]`. — Thom A, Jul 19 '18 at 09:47
Also, what are you trying to `INSERT`? Your `INSERT` clause has only 2 columns `group` and `account,` however, your `SELECT` has all of `A`'s column, as well as `title`, `amount`, and `id2` from `B`. 3 + *x* columns doesn't fit into 2. Also, you're trying to insert the data from `Table2` back into `table2` by use of the `INTO` clause? That makes no sense. What are you *really* trying to do here? Explain your goal, and provide sample data and expected results. — Thom A, Jul 19 '18 at 09:49

score 2 · Accepted Answer · answered Jul 19 '18 at 10:25

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

Insert into with output clause

1 Answers1