How to get last inserted id?

SQL Server and its quirks

Let's see how SQL Server behaves when retrieving the last inserted ID. SCOPE_IDENTITY() is your buddy here since it'll give you the last ID just the way you want it, unadulterated by any pesky triggers.

Inserting heaps of data or planning to utilize this ID real quick? Use OUTPUT INSERTED.ID right away, and dump the result into a variable or table. A future you will say thanks!

DECLARE @IDSandwich table( ID int );  -- yes, we are making an ID sandwich here

INSERT INTO YourTable (Name, Age)
OUTPUT INSERTED.ID INTO @IDSandwich  -- the ID is the filling
VALUES ('John', 21), ('Jane', 22);  -- don't forget the bread

SELECT ID FROM @IDSandwich;  -- delicious ID sandwich, anyone?


Now let's get nerdy with .NET. Always cast results from SqlCommand's ExecuteScalar() to your column's type when retrieving IDs:

Int32 freshId = (Int32)myCommand.ExecuteScalar();  // fresh IDs, anyone?


Your app's resource management ought to be like fine dining, so wrap everything in a using block and clean up nicely when done!

Safe from storms with your code

In the wild world of databases, always be prepared. Wrap those INSERT operations in try-catch blocks and log any unexpected SQL exceptions for a rainy day!

Don't invite SQL injection attacks to your party; it's a vibe killer. Use SqlCommand with parameters to keep your app's conversation with your database polite and secure:

using(SqlCommand cmd = new SqlCommand("INSERT INTO YourTable (Name, Age) VALUES (@name, @age); SET @ID = SCOPE_IDENTITY();", yourConnection))
{
    cmd.Parameters.AddWithValue("@name", "John Doe");  // John's our go-to guy
    cmd.Parameters.AddWithValue("@age", 30);  // He just had his birthday
    SqlParameter idParam = new SqlParameter("@ID", SqlDbType.Int);
    idParam.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(idParam);

    try
    {
        yourConnection.Open();
        cmd.ExecuteNonQuery();
        freshId = (Int32)cmd.Parameters["@ID"].Value;  // another fresh ID
    }
    catch(SqlException oops)  // oops, didn't see that coming
    {
        // Handle the oops
    }
    finally
    {
        yourConnection.Close();  // let's shut the door on our way out
    }
}


Yes, that's an output parameter retrieving the last inserted ID even if your table has triggers.

Unleash your SQL wizardry

Pull out your wizard robes when handling varied scenarios with inserting and retrieving IDs. Temporary tables come in handy when you're storing IDs from a batch operation or tackling tables loaded with triggers. Always proofread field names in OUTPUT INSERTED as a small typo might unleash the debugging kraken!

Sometimes duplicate records are about as welcome as a troll in a dungeon. Use an AcceptDuplicates flag to slam that door shut and keep your data neat and tidy.

Working with related tables? Use the retrieved ID with care to maintain relations ship-shape and Bristol fashion!