SQL Server Return the Value of Identity Column After Insert Statement

SQL Server - Return value after INSERT

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

Sql Server return the value of identity column after insert statement

Insert into TBL (Name, UserName, Password) Output Inserted.IdentityColumnName
 Values ('example', 'example', 'example')

How to get the identity of an inserted row?

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

How to get Identity value from SQL server after insert record

Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:

Replace the last concatenation with:

SQLString += "; SELECT SCOPE_IDENTITY();"

Then to retrieve the ID:

int ID = Convert.ToInt32(command.ExecuteScalar());

Getting SCOPE_IDENTITY() after insert with select

Since you are trying to retrieve multiple values here you need to use the OUTPUT clause. It will return all the newly inserted values into a table or table variable.

https://msdn.microsoft.com/en-us/library/ms177564.aspx

How do I get the identity column's value after INSERT?

I was able to get it to work in a somewhat dirty way. I'm not sure if this is the only way of doing this - I don't like to issue 2 statements to sql server since this can be risky in a parallel environment... But as a solution this does seems to work:

library(RODBC)

#semicolon at the end of the sql seems significant
query.str <- "
              INSERT INTO tbl(col1, col2, col3)
              VALUES (1, 2, 3);
             "
output <- sqlQuery(dbcon, query.str)
if (is.character(output) && length(output) > 0)
  stop(paste("SQL ERROR: ", output))

reg.id <- sqlQuery(dbcon, "SELECT SCOPE_IDENTITY()")
if (is.character(reg.id) && length(reg.id) > 0)
  stop(paste("SQL ERROR: ", reg.id))

reg.id[[1]]

Returning an identity after insert, then using identity value in another insert

If you don't want to use SCOPE_IDENTITY(), you can use following syntax:

DECLARE @out TABLE (pid int);

INSERT INTO patients (FirstName, LastName) 
OUTPUT inserted.pid INTO @out
VALUES ('Seth', 'Rollins');

INSERT INTO booking (pid, start, end) 
VALUES ((SELECT TOP 1 pid FROM @out), 'XX/XX/XXXX', 'XX/XX/XXXX');

Store the value of output inserted._ID to local variable to reuse it in another query

IDENTITY COLUMN

If it is an identity column and you are only inserting a single row then you can use SCOPE_IDENTITY() function to get the last generated Identity value within the scope of the current session.

DECLARE @NewValue INT;
insert into V_Post 
values('Please return the id of THIS row :)')
SET @NewValue = SCOPE_IDENTITY()

IF NOT IDENTITY Column Or Multiple Identity values

If it is an identity column and you are inserting multiple rows and want to return all the newly inserted Identity values, or it is not an identity column but you need the last inserted value then you can make sure of OUTPUT command to get the newly inserted values.

DECLARE @tbl TABLE (Col1 DataType)
DECLARE @NewValue Datatype;

insert into V_Post 
output inserted._ID INTO @tbl
values('Please return the id of THIS row :)')

SELECT @NewValue = Col1 FROM @tbl

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

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.

Returning the value of an identity column during/after SQL INSERT command

The Insert method uses the InsertCommand property...

So modify the InsertCommand to be either

INSERT Mytable (col1, col2) VALUES (@param1, @parame);SELECT SCOPE_IDENTITY();

Or (for SQL Server 2005+)

INSERT Mytable (col1, col2) OUTPUT INSERTED.IDCol VALUES (@param1, @param2);