Combining data with SELECT clause with OUTPUT clause

Question

DECLARE @Temp TABLE (col_1 INT, col_2 INT, col_3 INT, col_4 INT)

INSERT INTO table_1(col_1, col_2, col_3)
OUTPUT INSERTED.val_1, INSERTED.val_2, INSERTED.val_2, INSERTED.val_3, tab.val_4 INTO @Temp(col_1, col_2, col_3, col_4)
SELECT tab.val_1,
    tab.val_2,
    tab.val_3
FROM table_2 AS tab
WHERE tab.something > 0

I need to insert values from table_2 to table_1 and @Temp table. The problem is table_1 doestn't have a column which is available in @Temp and I couldn't use find a way to use OUTPUT clause which should also use data from the SELECT clause used in table_2.

When I try the above code I get an error,

The multi-part identifier "tab.val_4" could not be bound

Please ask me in the comments if the question is not understandable or you need some clarification — Viki, Feb 01 '22 at 07:49
You can use *from_table_name* only in DELETE, UPDATE, or MERGE statement. Consider updating `@Temp `after INSERT provided you have UNIQUE keys in both `@Temp` and `table_2`. — Serg, Feb 01 '22 at 08:15

score 2 · Answer 1 · edited Feb 01 '22 at 10:13

MERGE statement (instead of INSERT) can do this:

MERGE INTO table_1 t1
USING (
    select  
         val_1 as col1,
         val_2 as col2,
         val_3 as col3,
         val_4 as col4
    from table_2 tab 
    where tab.something > 0
) t2
   on 1=2 -- so that all t2 records are treated as 'no match'
WHEN NOT MATCHED BY TARGET THEN
    INSERT (col1, col2, col3)
    values (t2.col1, t2.col2, t2.col3)
OUTPUT INSERTED.col1, INSERTED.col2, INSERTED.col3, t2.col4
  INTO @Temp (col_1, col_2, col_3, col_4)
;

I have not tested it extensively..

score 1 · Answer 2 · answered Feb 01 '22 at 08:15

You can use 2 INSERT statements:

DECLARE @Temp TABLE (col_1 INT, col_2 INT, col_3 INT, col_4 INT);


INSERT INTO @Temp(col_1, col_2, col_3, col_4)
SELECT tab.val_1,
    tab.val_2,
    tab.val_3,
    tab.val_4
FROM table_2 AS tab
WHERE tab.something > 0;

INSERT INTO table_1(col_1, col_2, col_3)
SELECT col_1, col_2, col_3
FROM @Temp;

Combining data with SELECT clause with OUTPUT clause

2 Answers2