Will SCOPE_IDENTITY Work in this Case?

Question

I have PK that is self incrementing key. I need to insert the record into the database and then get that PK back and use it in another insert.

However I would like to do this in one transaction. Is that possible. The idea is that if something fails in any of the updates/inserts I have to do then I can rollback everything but I am under the impression that I need to do a commit.

I was going to do it in ado.net at first but then switched to a stored procedure since I thought maybe that would get around this issue.

Will a SP help me out in this case?

Yemane

Answer 1

Yes, scope_identity will give you the latest inserted id. As an alternative, if you're using sql server 2005+ you can use the output clause.

INSERT INTO [MyTable]([MyCol])
OUTPUT INSERTED.ID
SELECT [MyCol] FROM [MySourceTable];

Whack

Answer 2

How about:

BEGIN TRANSACTION
BEGIN TRY

   INSERT INTO dbo.YourFirstTable(.....)
   VALUES(.......)

   DECLARE @newID INT
   SELECT @newID = SCOPE_IDENTITY()

   INSERT INTO dbo.YourSecondTable(ID, .......)
   VALUES(@newID, ........)

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

Should work in any version of SQL Server 2005 or newer.

Just by fetching the SCOPE_IDENTITY() value, you're definitely not "breaking" the transaction ... wrap this into e.g. a stored procedure, or just call it from your calling code.

Accomplished

@chobo2: put it into a stored procedure and call that procedure - that's by far the easiest solution ... – Accomplished

Ok if it is easier. 2 questions. What happens if you wrap the ado.net stored procedure in a transaction and rollback. Do you still need it in the SP? Second what happens if you need to ids back from 2 inserts will Scope_Identity work still? – Yemane

@chobo2: SCOPE_IDENTITY() returns the last inserted IDENTITY value - the last ONE value. If you insert two rows - no, then it doesn't work (just returns the last value inserted). Not sure what you mean by "wrapping" this in a transaction - there already is a transaction here. But if there's a rollback inside this stored procedure, then any "wrapping" transaction will also be rolled back - SQL Server doesn't really have true nested / independent transactions... – Accomplished

Hmm how can I do it then can't I do: insert, scope, insert scope :) – Yemane

@chobo2: in that case you need the OUTPUT clause to output the newly inserted ID - along with something else that can be used later on - into a e.g. table variable (like "flem" showed in his answer) – Accomplished

would the output break the transaction? Seems like the record is already inserted at that time. – Yemane

@chobo2: NO - why are you thinking everything breaks a transaction? Unless a ROLLBACK happens - nothing "breaks" the transaction ..... – Accomplished

don't you need to do a commit for the record to be inserted and get a generated PK. As I said I use nhibernate. To get a generated PK the record would have to be committed and that needs to be done at the server. Once a commit happens that transaction is done and you got to start a new one. Thus breaking the transaction – Yemane

@chobo2: NO - the data will be inserted - just not yet committed. But when it's inserted, the new IDENTITY values are being defined - you can read those out. Now if the transaction ends up being rolled back in the end, those PK identity values will be "forgotten" (e.g. they were used, but rolled back) and that's how gaps can happen in an IDENTITY column. But you definitely need to read up on transactions and transaction handling in SQL Server! Check the relevant MSDN documentation. – Accomplished