TSQL - select inserted rows

Question

Is there a way to select inserted row? I am trying to run the following query using SqlCommand (it is used to reserve given id in the database):

INSERT INTO tbl (id)
SELECT COUNT(*) + 1 AS id from tbl

Is there a way to return the inserted id column so I can use it in my app? Or maybe is there an easier way I can achieve this? I have to be absolutely sure I reserve the free id, even if multiple users will use the app at the same time.

Also, is there a way I can change the query to pick first free id so I can avoid gaps?

Answer 1

You can easily use the OUTPUT clause to output e.g. the newly created ID's as a result set, and you can read those from your C# app use a standard SqlDataReader:

INSERT INTO tbl (id)
OUTPUT Inserted.Id
   SELECT COUNT(*) + 1 AS id from tbl

Update: the data returned by the OUTPUT clause can be captured from the C# side as if it were a regular SELECT statement:

string insertStmt = "INSERT INTO tbl (id) " + 
                    " OUTPUT Inserted.Id " + 
                    " SELECT COUNT(*) + 1 AS id from tbl";

using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmd = new SqlCommand(insertStmt, conn))
{
    conn.Open();

    // execute your INSERT statement into a SqlDataReader
    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        // read the values returned from the OUTPUT clause
        while(reader.Read())
        {
            int insertedID = reader.GetInt32(0);
            // do something with those values....                
        }
    }

    conn.Close();
}