OUTPUT INTO fails due to invalid columns name

Question

I am trying to make two inserts one after another like:

INSERT INTO tbl_tours (TimeFrom)
OUTPUT inserted.tourId, DispatchingId, TimeFrom, TimeTo INTO tbl_tourData (tour_fk, dispatchId, timeFrom, timeTo)
    SELECT TimeFrom 
    FROM @tmpTable

SELECT * FROM tbl_tours
SELECT * FROM tbl_tourData

But I get an error:

Msg 207 Level 16 State 1 Line 13
Invalid column name 'DispatchingId'.

Msg 207 Level 16 State 1 Line 13
Invalid column name 'TimeFrom'.

Msg 207 Level 16 State 1 Line 13
Invalid column name 'TimeTo'.

You can check full code at this fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=c10f9886bcfb709503007f18b24eabfd

How to combine these inserts?

Does this answer your question? [Is it possible to for SQL Output clause to return a column not being inserted?](https://stackoverflow.com/questions/10949730/is-it-possible-to-for-sql-output-clause-to-return-a-column-not-being-inserted) — Dale K, Jul 28 '20 at 20:36

score 1 · Answer 1 · answered Jul 28 '20 at 19:30

1

The output clause can only refer to columns that are inserted. So this works:

INSERT INTO tbl_tours (TimeFrom)
    output inserted.tourId, inserted.TimeFrom into tbl_tourData(tour_fk, timeFrom)
    SELECT TimeFrom FROM @tmpTable;

Here is the revised db<>fiddle.

If you want additional information, you need to join back to another source.

answered Jul 28 '20 at 19:30

Gordon Linoff

1,242,037
58
646
786

Upvote. But I gave the same information " The #AUDIT Table can only contain pieces of information from your PARENT INSERT (any properties you have and of course the new IDENTITY, which is what you are after )" . at this question : https://stackoverflow.com/questions/63139496/how-to-make-insert-after-insert-and-combine-data-with-result-ids-from-first-ins/63139656#63139656 – granadaCoder Jul 28 '20 at 19:33
Does this really work? `inserted.tourid` isn't a column that's inserted into the target table. I think you need to use a MERGE instead as per https://stackoverflow.com/a/10950418/8479 – Rory Oct 08 '21 at 18:10

allmhuran · Answer 2 · 2020-07-28T19:46:03.707

When you do an insert ... output, the "output" part can only output whatever was inserted by the "insert" part. You can't reference data from the "inserting" table.

You do insert into tbl_tours(TimeFrom). So you're only inserting a single column - the TimeFrom column, and the tour_id column will be automatically inserted, so that's available too. But then you try to use 4 columns in the output list. Where would these extra two columns come from?

One way to do this in a single step is to use the merge statement, which can get data from the "inserting" source, not just the "inserted" table. Since you know you always want to do an insert, you can join on 1 = 0:

merge   tbl_tours 
using   @tmpTable tmp on 1 = 0
when    not matched then
insert  (TimeFrom)
values  (tmp.TimeFrom)
output  inserted.tourId,
        tmp.dispatchingId,
        inserted.timeFrom, -- or tmp.timeFrom, doesn't matter which
        tmp.TimeTo
into    tbl_tourData (tour_fk, dispatchId, timeFrom, timeTo);

I should add: This is only possible because you don't actually have a foreign key defined from tbl_tourData to tbl_Tours. You probably do intend to have one given your column name. An output clause can't output into a table with a foreign key (or a primary key with a foreign key to it), so this approach won't work at all if you ever decide to actually create that foreign key. You'll have to do it in two steps. Either per Gordon's answer (insert and join), or by creating a whole new temp table matching the schema of tbl_tourData, outputting everything into that using merge, and then dumping the second temp table into the real tbl_tourData.

OUTPUT INTO fails due to invalid columns name

2 Answers2