Executing two INSERT an the same stored procedure with a SCOPE_IDENTITY

Question

Hi !

I would like to insert a record into my SQL Server database with a stored procedure and catch the ID of this inserted row (SCOPE_IDENTITY()) to use it in another insert command.

Because I would like to execute those two select command the one after the other if the one didn't pass the other didn't pass too !

Any Idea ?

Thank you for helping me !

Answer 1

You need to do something like this:

DECLARE @NewID INT

INSERT INTO dbo.FirstTable(Col1, Col2, ...., ColN)
   VALUES(Value1, Value2, ....., ValueN)

SELECT @NewID = SCOPE_IDENTITY()

INSERT INTO dbo.SecondTable(IDColumn, ColA, ColB, .., ColZ)
  VALUES(@NewID, ValueA, ValueB, ...., ValueZ)

That should insert the first set of data into dbo.FirstTable, grab the newly created ID into @NewID and then insert the second batch of data using that new ID value.