CODEWITHSUNDEEP
sql-servert-sqlsql-update
Irmantas Želionis
Irmantas Želionis

Reputation: 2324

Use OUTPUT from INSERT INTO in UPDATE

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?

Upvotes: 0

Views: 770

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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.

Upvotes: 2

Related Questions