Will SCOPE_IDENTITY Work in this Case?

sql sql-server ado.net scope-identity
chobo2 picture chobo2 · Oct 10, 2012 · Viewed 7.3k times · Source

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?

Answer

Paul Fleming picture Paul Fleming · Oct 10, 2012

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];

Related questions

SET NOCOUNT ON usage

Inspired by this question where there are differing views on SET NOCOUNT... Should we use SET NOCOUNT ON for SQL Server? If not, why not? What it does Edit 6, on 22 Jul 2011 It suppresses the "xx rows affected" message after any …

Read More
Inserting values into a SQL Server database using ado.net via C#

I have created a simple program to insert values into the table [regist], but I keep getting the error Incorrect syntax near ')' on cmd.ExecuteNonQuery();: private void button1_Click(object sender, EventArgs e) { SqlConnection cn = new SqlConnection("Data …

Read More
SqlBulkCopy from a List<>

How can I make a big insertion with SqlBulkCopy from a List<> of simple object ? Do I implement my custom IDataReader ?

Read More