When using Sql Server to insert records into a DB its a common requirement to be able to pull back the auto incremented Id that was generated for this row. Most solutions for this i have seen involve making a second query to the database in and along the lines of “SELECT @@IDENTITY”, but this method seems quite wasteful. It seems like a simple request that the database would return the value automatically without you having to ask for it with a second wasteful query. Well, it turns out that it’s incredibly simple to do the auto increment ID from SQL Server after an insert is performed.
The auto incremented value can be easily returned by using the OUTPUT Inserted.<auto id col name>. To put this in a real life example, the following query will insert a user into the Users database and will return the auto ID. In this case there is a column called “id” and this is what i am asking the DB to return in the query.
INSERT INTO Users(name, age) OUTPUT Inserted.id VALUES ('Tom', '50');
Using this query from whatever language you use will make the DB return the auto incremented ID from an SQL Server database. Here is an example of how you would use the above query in C# to get the auto incremented ID back from the database.
using(OleDbCommand cmd = new OleDbCommand("INSERT INTO Users(name, age) OUTPUT Inserted.id VALUES ('Tom', '50')"))
{
cmd.Connection = myCon;
myCon.Open();
int scheduleid = (int)cmd.ExecuteScalar();
}