Use OUTPUT from INSERT INTO in UPDATE

Question

I am populating a new table from another two tables. But I also try to use the just inserted entity ID to link it with one of the tables.

I successfully get the inserted entity ID like this:

INSERT INTO EngagementOverview (AccountingFirmId, AccountingFirmClientId, CompanyName, YearEnd, YearStart, AccountingFirmOfficeId)
OUTPUT inserted.id 
SELECT Engagement.AccountingFirmId, Engagement.AccountingFirmClientId, CompanyName, YearEnd, YearStart, AccountingFirmOfficeId
FROM Engagement
INNER JOIN EngagementHeader ON Engagement.EngagementHeaderId = EngagementHeader.Id

How could I use that in UPDATE statement to update EngagementHeader table's field?

Tag your question with the database you are using. – Gordon Linoff Oct 22 '19 at 13:31 — Gordon Linoff, Oct 22 '19 at 13:31

score 2 · Accepted Answer · answered Oct 22 '19 at 13:32

2

You would store the value in a table. Then you can use the table in subsequent processing.

For instance:

DECLARE TABLE @ids (id int);

INSERT INTO EngagementOverview (AccountingFirmId, AccountingFirmClientId, CompanyName, YearEnd, YearStart, AccountingFirmOfficeId)
    OUTPUT inserted.id INTO @ids
    SELECT e.AccountingFirmId, e.AccountingFirmClientId, CompanyName, YearEnd, YearStart, AccountingFirmOfficeId
    FROM Engagement e INNER JOIN
         EngagementHeader eh
         ON e.EngagementHeaderId = eh.Id;

I'm not sure what update you want an EngagementHeader.

answered Oct 22 '19 at 13:32

Gordon Linoff

1,242,037
58
646
786

Is there a way to add more data to the @ids table together with OUTPUT. I would love to have id and EngagementHeader.Id in the same row – Irmantas Želionis Oct 22 '19 at 14:04
@IrmantasŽelionis . . . I believe you can only add columns in the *output* table. This is a pain. One solution may be that the other columns identify a unique key and you can query them, with `EngagementHeader` afterwards. – Gordon Linoff Oct 22 '19 at 14:24
@IrmantasŽelionis Not exactly clear what you want, but `merge` allows `output` of columns beyond those which are being inserted. [This](https://stackoverflow.com/questions/41184310/insert-into-merge-select-sql-server) answer may be helpful. – HABO Oct 22 '19 at 19:30

Use OUTPUT from INSERT INTO in UPDATE

1 Answers1