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.

+3


source to share


1 answer


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

+5


source







All Articles