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?

Why are you generating and reserving IDs in the first place? It sounds like the problem you actually want to solve is why you are doing this. Databases already have auto-id columns that will generate a new unique ID when you do an insert. — Chris, May 19 '13 at 19:58
How else can I ensure I will operate on a single row when I will be doing a bunch of updates which are related to that new row and the only key column is the ID one? — kyooryu, May 19 '13 at 20:01
http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row might be of interest in how to get the identity once you have inserted data. This way the database can generate an id once you insert the data and you can retrieve it for whatever else you want to do next. — Chris, May 19 '13 at 20:05

marc_s · Accepted Answer · 2013-05-19T21:56:23.253

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();
}

Could you elaborate how can I capture the output? As far as I know SqlCommand can either return data or manipulate it, not both. — kyooryu, May 19 '13 at 21:49
Super handy to use inside SSMS as well! `OUTPUT Inserted.*` shows everything you just inserted. Great tip, thanks! — roufamatic, Jul 08 '15 at 20:25

Semih Yagcioglu · Answer 2 · 2013-07-09T16:24:01.993

0

If id is an identity column, you can use SCOPE_IDENTITY() or MAX(id) to get the latest record id, but this won't solve anything. Another record may be inserted while you get that record. Also if you want to get the inserted row, one way to do that is via Triggers or via inserted keyword. But trying to reserve the id is useless, since identity columns returns a unique id each time.

edited Jul 09 '13 at 16:24

answered May 19 '13 at 20:00

Semih Yagcioglu

4,011
1
26
43

SCOPE_IDENTITY() is not Global, so if you are only inserting one record you could use it in scope to get the id value. You don't have to worry about other scopes inserting at the same time. See more here http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide#1920640 – Gary Mar 22 '17 at 14:26

TSQL - select inserted rows

2 Answers2

Related