Stored procedure for insert record and for each start update statement
I would like to have a stored procedure that inserts rows into a table (retrieved from a select query from another table) and for each newly inserted row gets its identity and updates the original table with id
Pseudocode
records = select id,city,state,country from USER where name=@name
for each record in records // for each rows selected
insert into LOCATION(city,state,country) values(@record.city,@record.state,@record.country); //inserts a value into LOCATION table
@id = SCOPE_IDENTITY(); // gets the identity of the newly inserted row
update USER set LocationId=@id where Id=@record.id //updates the new id back to old table column
end
This is a data transfer task where we want to allocate LOCATION from the USER table
Thanks in advance for your time and effort for this topic.
source to share
You can do something like this:
DECLARE @InsertedValues TABLE (ID INT, City VARCHAR(50), State VARCHAR(50), Country VARCHAR(50))
INSERT INTO dbo.Location(City, State, Country)
OUTPUT Inserted.ID, Inserted.City, Inserted.State, Inserted.Country INTO @InsertedValues(ID, City, State, Country)
SELECT City, State, Country
FROM dbo.YourSourceTable
That being said, you now have the inserted values , including the new identifier values, in your table variable @InsertedValues
, and you can now update the original table as you see fit.
UPDATE dbo.YourSourceTable
SET
Col1 = iv.Col1,
Col2 = iv.Col2, -- etc. - do whatever you nee to do here!
FROM @InsertedValues iv
WHERE ......... -- here, you need some condition to link the inserted values to the original table
It doesn't require any cursor or any other messy RBAR processing (line by agonizing line) - everything is fine tuned based on the set and as fast as it can get.
More about the offer OUTPUT
on MSDN SQL Server Books Online - you can use the offer OUTPUT
in insert, update, and even delete operations
source to share