Sunday, August 22, 2010

Determine new PK after an Insert

Today’s blog post will be another excerpt from my .Net Tips column in the April 2008 Issue of the Universal Thread Magazine.

You have a couple of options to get the PK value of a newly inserted record. I am assuming SQL Server database in these examples.

First, if you are not using Stored Procs and simply sending an INSERT INTO statement, just add a "SELECT SCOPE_IDENTITY() at the end of your query and be sure to execute your query by reading the result back (into a datareader or dataset for example).

Your query string should look like this:

string Sql = "INSERT INTO MyTable (ColumnOne) VALUES ('Bob') SELECT SCOPE_IDENTITY() AS MyPK";

And, here's the code that shows how to execute and read the results back using a DataReader:

int MyPK = 0;
SqlCommand sc = new SqlCommand(Sql, new SqlConnection(this.TestConnection));
SqlDataReader rd = sc.ExecuteReader(CommandBehavior.CloseConnection);

while (rd.Read())
MyPK = Convert.ToInt32(rd["MyPK"]);

Alternatively, if you're using a Stored Proc, you'd use an OUTPUT parameter in your Stored Proc, and a ParameterDirection.InputOutput in your code:

-- The Stored Proc
@ColumnOne char(8) = NULL,
@ColumnTwo char(4) = NULL
INSERT MyTable (ColumnOne, ColumnTwo)
SELECT @ColumnOne, @ColumnTwo


And here's how you would call it in your code:

Command.CommandText = "MySP";
Command.Parameters.Add("@PK", 0);
Command.Parameters["@PK"].Direction = ParameterDirection.InputOutput;
Command.Parameters.Add("@ColumnOne", OneValue);
Command.Parameters.Add("@ColumnTWo", TwoValue);


MyNewPK = Command.Parameters["@PK"].Value;

Thanks to Éric Moreau (and another post by me) in Messages #1090511 and #930339 on the Universal Thread.


  1. Hi, Bonnie,

    There are several ways to handle this, and the SCOPE_IDENTITY approach is certainly tried and true.

    However, suppose the row you're INSERTing contains derived columns (i.e. columns with default expressions, columns that are calculated in a trigger, etc.) It's not unrealistic to insert five values into a new row for a table with 8 columns. So a developer might want to bring back more than just the identity key.

    In that instance, you can slap an OUTPUT statement (added in SQL 2005) into an INSERT. The OUTPUT statement allows you to tap into the INSERTED system table. So I can do the following...

    INSERT INTO MyTable (ColumnList)
    OUTPUT Inserted.*
    VALUES (ValueList)

    And the insert statement will give you a snapshot of the row after the insert finishes. This can be very helping in getting the state of the row after the insert, and potentially eliminates the need to make a round trip back to the server to query the new row from the value returned by scope_identity.


  2. Good points Kevin! Thanks for adding some more ideas for my readers!

    ... Assuming I have readers ... and assuming they read the comments! ;0)