CODEWITHSUNDEEP
sqlsql-server-2005insert
Yatrix
Yatrix

Reputation: 13805

How do I use an INSERT statement's OUTPUT clause to get the identity value?

If I have an insert statement such as:

INSERT INTO MyTable
(  
  Name,
  Address,
  PhoneNo
)
VALUES
(
  'Yatrix',
   '1234 Address Stuff',
   '1112223333'
)

How do I set @var INT to the new row's identity value (called Id) using the OUTPUT clause? I've seen samples of putting INSERTED.Name into table variables, for example, but I can't get it into a non-table variable.

I've tried OUPUT INSERTED.Id AS @var, SET @var = INSERTED.Id, but neither have worked.

Upvotes: 309

Views: 461722

Answers (2)

Andrew Mather
Andrew Mather

Reputation: 11

I didn't like the 'trust me, it's the last ID somewhere' element of the above, so browsed Learn.Microsoft and with a bit of jiggling got a neat and exact solution, informed by Micrsosoft examples. (You might not like the old-man-style code, but hey, whatever). It's a C# function but you can extract the SQL elements as required.

const string queryString =
    "DECLARE @MyTableVar TABLE(NewParaID INT); "
    + "INSERT into dbo.Para(ParaText) "
    + "OUTPUT INSERTED.ParaID INTO @MyTableVar "
    + "VALUES(@para); "
    + "SELECT NewParaID FROM @MyTableVar; ";

using (SqlConnection connection =
new(m_sConnectionString))
{
    SqlCommand command = new(queryString, connection);
    command.Parameters.AddWithValue("@para", sPara);
    
    try
    {
        connection.Open();

        nParaID = (int)command.ExecuteScalar();
    }
    catch (Exception ex)
    {
        sError = ex.Message;
        bResult = false;
    }
}

Upvotes: -2

marc_s
marc_s

Reputation: 755451

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar() (instead of .ExecuteNonQuery()) to read the resulting ID back.

Or if you need to capture the newly inserted ID inside T-SQL (e.g. for later further processing), you need to create a table variable:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

This way, you can put multiple values into @OutputTbl and do further processing on those. You could also use a "regular" temporary table (#temp) or even a "real" persistent table as your "output target" here.

Upvotes: 615

Related Questions