How to insert a record and return the newly created ID using a single SqlCommand?

Question

I'm using an SqlCommand object to insert a record into a table with an autogenerated primary key. How can I write the command text so that I get the newly created ID when I use the ExecuteScalar() method?

Answer 1

insert into Yourtable()
values()
SELECT SCOPE_IDENTITY()

I just ran a test and verified that the semi-colons are optional using SQL Server 2005 SP2, and .Net 3.5

Answer 2

Straight out of the Whirlpool:

If you're using MS SQL you can use "SELECT @@IDENTITY as Value" after your insert to get the last ID generated

and:

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

Edit: As pointed out in the comments, you should always use SCOPE_IDENTITY, not @@IDENTITY.

Answer 3

INSERT INTO YourTable(val1, val2, val3 ...) VALUES(@val1, @val2, @val3...); SELECT SCOPE_IDENTITY();

Don't forget the semicolons at the end of each statement.

Answer 4

Add the following line to the end of the Sql Query...

SELECT SCOPE_IDENTITY()

And then use the ExecuteScalar method on the SqlCommand object...

var rowCount = command.ExecuteScalar()

Answer 5

Immediately after your insert stmt, use

SELECT CAST(scope_identity() AS bigint) ---- incase you have a return result as int64

This will return the column created id/identity.

Answer 6

Don't use @@IDENTITY, however simple it may seem. It can return incorrect values.

SELECT SCOPE_IDENTITY()

appears to be the obvious choice.

Answer 7

Add an output parameter to the command object and then set the value to the new ID in the stored procedure.

Stored Procedure:

@ID AS INT OUTPUT

[Insert Command]

SET @ID = SCOPE_IDENTITY()

.NET:

cmd.CommandText = "stored_procedure";

SqlParameter pID = new SqlParameter("ID", DBType.Int32, 4);

pID.Direction = ParameterDirection.Output;

cmd.ExecuteScalar();

int id = Convert.ToInt32(cmd.Parameters["ID"].Value.ToString());

Answer 8

Although I like Dave Markle's answer, ( and I see you did too, since you marked it as your answer ), that method can fail if you have triggers on your database, that audit CUD operations, and your audit table has an IDENTITY column. It would return the value of the Audit table's identity, not the table you just inserted into, since the audit table actualy happen after.

In that case, a more generic method can be used that will work in both cases, regardless of any auditing. Its a bit more wordy, but you get what you pay for.

example:

@"DECLARE @tmp AS TABLE ( id int )
                    INSERT INTO case
                    (
                        caseID,
                        partID,
                        serialNumber,
                        hardware,
                        software,
                        firmware
                    )
                    OUTPUT Inserted.ID into @tmp
                    VALUES
                    (
                        @caseID,
                        @partItemID,
                        @serialNumber,
                        @hardware,
                        @software,
                        @firmware
                    )
                Select ID from @tmp" )