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.
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