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

Question

I use the SqlCommand object to insert a record into a table using an auto-generated primary key. How to write command text so that I get a newly created identifier when I use the ExecuteScalar () method?

Answer 1

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

Do not forget the semicolons at the end of each statement.

Answer 2

Add the following line at the end of the Sql query ...

 SELECT SCOPE_IDENTITY() 

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

 var rowCount = command.ExecuteScalar() 

Answer 3

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

I just checked and checked that half-columns are optional using SQL Server 2005 SP2 and .Net 3.5

Answer 4

Add an output parameter to the command object, and then set the value to the new identifier 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 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 created id / identity column.

Answer 6

Do not use @@ IDENTITY, no matter how simple it may seem. It may return incorrect values.

 SELECT SCOPE_IDENTITY() 

seems like an obvious choice.

Answer 7

Straight from Whirlpool :

If you are using MS SQL, you can use "SELECT @@ IDENTITY as Value" after your insert to get the last generated identifier

and

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

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

Answer 8

Although I like the answer of Dave Markle (and I see that you did it too, since you marked it as your answer), this method may fail if you have triggers on your database in which CUD operations are checked, and Your audit table has an IDENTITY column. It will return the id value of the audit table, not the table you just pasted into, since the actual audit table happened after.

In this case, you can use a more general method, which will work in both cases, regardless of any audit. It's a little more verbose, 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" ) 

Answer 9

If your identifier is Guid, then I found this solution to be the best:

 INSERT INTO YourTable (val1, val2, val3) OUTPUT inserted.id VALUES (@val1, @val2, @val3) 

Thanks @Scott Ivey

Full demo:

  internal static Guid InsertNote(Note note) { Guid id; using ( var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString)) { connection.Open(); using ( var command = new SqlCommand( "INSERT INTO Notes ([Title],[Text]) " + "OUTPUT inserted.id " + $"VALUES ('{title}','{text}');", connection)) { command.CommandType = CommandType.Text; var reader = command.ExecuteReader(); reader.Read(); id = reader.GetGuid(reader.GetOrdinal("id")); } connection.Close(); } return id; } 

I would recommend using a stored procedure, but this is for unit testing our repository.