Tutorial :How do I return a new IDENTITY column value from an SQLServer SELECT statement?



Question:

I'm inserting into an SQLServer table with an autoincrementing key field. (I believe this is called an IDENTITY column in SQLServer.)

In Oracle, I can use the RETURNING keyword to give my INSERT statement a results set like a SELECT query that will return the generated value:

INSERT INTO table  (foreign_key1, value)  VALUES  (9, 'text')  RETURNING key_field INTO :var;  

How do I accomplish this in SQLServer?

Bonus: Okay, nice answers so far, but how do I put it into a single statement, if possible? :)


Solution:1

In general, it can't be done in a single statement.

But the SELECT SCOPE_IDENTITY() can (and should) be placed directly after the INSERT statement, so it's all done in the same database call.

Example:

mydb.ExecuteSql("INSERT INTO table(foreign_key1, value) VALUES(9, 'text'); SELECT SCOPE_IDENTITY();");  

You can use OUTPUT, but it has some limitations you should be aware of:

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


Solution:2

SELECT SCOPE_IDENTITY()  

Edit: Having a play...

If only the OUTPUT clause supported local variables.

Anyway, to get a range of IDs rather than a singleton

DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))    INSERT @Mytable (valuecol)   OUTPUT Inserted.keycol  SELECT 'harry'  UNION ALL  SELECT 'dick'  UNION ALL  SELECT 'tom'  

Edit 2: In one call. I've never had occasion to use this construct.

DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))    INSERT @Mytable (valuecol)   OUTPUT Inserted.keycol  VALUES('foobar')  


Solution:3

In addition to @@IDENTITY, you should also look into SCOPE_IDENTITY() and IDENT_CURRENT(). You most likely want SCOPE_IDENTITY(). @@IDENTITY has a problem in that it might return an identity value created in a trigger on the actual table that you're trying to track.

Also, these are single-value functions. I don't know how the Oracle RETURNING keyword works.


Solution:4

SCOPE_IDENTITY


Solution:5

It depends on your calling context.

If you're calling this from client code, you can use OUTPUT and then read the value returned.

DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))    INSERT INTO @t (ColStr)  OUTPUT Inserted.ColID  VALUES ('Hello World')  

Result:

      ColID  -----------            1  

If you're wrapping this in a stored procedure, using OUTPUT is more work. There, you'll want to use SCOPE_IDENTITY(), but you can't do it in a single statement. Sure, you can put multiple statements on a single line with a ';' separator, but that's not a single statement.

DECLARE @idValue    int  DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))    INSERT INTO @t (ColStr) VALUES ('Hello World')    SELECT @idValue = SCOPE_IDENTITY()  

Result: @idValue variable contains identity value. Use an OUTPUT parameter to return the value.


Solution:6

You can use OUTPUT INTO, which has the additional benefits of being able to capture multiple identities inserted.


Solution:7

INSERT INTO table(foreign_key1, value)VALUES(9, 'text');SELECT @@IDENTITY;


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »