How to use more than one table with OUTPUT clause?

Question

Is it possible to insert columns of different tables in the OUTPUT clause of an INSERT statement, such as:

DECLARE @insertedrecords TABLE (Id int, [Guid] uniqueidentifier);

INSERT INTO mytable 
    (column names here...)
OUTPUT inserted.id_no, b.[Guid] INTO @insertedrecords 
SELECT 
    column names here...
FROM @myTVP b

Currently, using the above I get the following error:

The multi-part identifier "b.Guid" could not be bound.

You can use: OUTPUT inserted.id_no, inserted.[Guid] INTO @insertedrecords — cloudsafe, Dec 28 '16 at 14:28
Post the original query. It depends actually are you inserting `b.[Guid]` into `mytable` table ? — Pரதீப், Dec 28 '16 at 15:07

score 0 · Answer 1 · answered Dec 28 '16 at 15:36

Inserted special table will hold the records those are inserted into the Target table not all the data from Source table. When a column from target table is not part of Insert list then in OUTPUT clause it will be NULL

So you need two different Inserts

DECLARE @insertedrecords TABLE (Id int, [Guid] uniqueidentifier);

INSERT INTO mytable 
    (column names here...)
SELECT 
    column names here...
FROM @myTVP b

Insert into @insertedrecords(Id,Guid)
select id_no, [Guid] 
From @myTVP

How to use more than one table with OUTPUT clause?

1 Answers1