CODEWITHSUNDEEP
sql-server
Dendrobates
Dendrobates

Reputation: 3534

MSSQL update set from output clause

Context I got a temporary table which is filled/adjusted by users. Let's call the table tmp, with columns ID, updated_at, price, foreign_ID. Every time the user enters a new price, the price column is filled and the updated_at and ID are created automatically. foreign_ID is NULL until the record is processed to another table, when my foreign_ID should contain the ID of the other table.

Periodically I update a table with prices, let's call it prices. Here are all prices stored from different sources, among them from the tmp table. The prices table has the columns ID, updated_at, price.

question I want to insert the data from my tmp table into prices table, and update the column foreign_ID with the corresponding ID from my prices table. How can I insert new rows in a table and update/set a IDs in another table?

My desired result after the periodic update is a new entry in the prices table with the new prices which were not yet processed, and a foreign_ID in my tmp table which corresponds with the ID in my prices table.

I know I can output the inserted IDs using the following query:

insert into prices
output inserted.ID
select price
from tmp;

I'm struggling to see how I can use the inserted.ID to update my tmp.foreign_ID column with the output above.

Help is appreciated!

Upvotes: 0

Views: 740

Answers (1)

Thom A
Thom A

Reputation: 95658

You can also INSERT the values from the OUTPUT clause into another table as well, so if you need those you can still reference them.

Without proper sample data and behaviour, this is just an example but should, hopefully, set you on the right path as it shows you how to get the values from inserted into another object. You can then use that object to do the additional task(s) you need:

CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
                            SomeString varchar(10));
GO


DECLARE @IDs table (ID int);

INSERT INTO dbo.SomeTable (SomeString)
OUTPUT inserted.ID
INTO @IDs (ID)
VALUES('asdjgkde'),('sdflhdglf');

SELECT *
FROM @IDs;
GO

DROP TABLE dbo.SomeTable;

Upvotes: 1

Related Questions