How do I return a new IDENTITY column value from a

问题:

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? :)

回答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

回答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')

回答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.

回答4:

SCOPE_IDENTITY

回答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.

回答6:

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

回答7:

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