- The
@@identityfunction returns the last identity created in the same session. - The
scope_identity()function returns the last identity created in the same session and the same scope. - The
ident_current(name)returns the last identity created for a specific table or view in any session. - The
identity()function is not used to get an identity, it's used to create an identity in aselect...intoquery.
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.
So, normally you would use the scope_identity() function.
No, SCOPE_IDENTITY() only gives you the one, latest inserted IDENTITY value. But you could check out the OUTPUT clause of SQL Server ....
DECLARE @IdentityTable TABLE (SomeKeyValue INT, NewIdentity INT) INSERT INTO [MyTable] OUTPUT Inserted.Keyvalue, Inserted.ID INTO @IdentityTable(SomeKeyValue, NewIdentity) VALUES ('1'), ('2'), ('3')
Once you've run your INSERT statement, the table variable will hold "some key value" (for you, to identify the row) and the newly inserted ID values for each row inserted. Now go crazy with this! :-)
其中Inserted是和Output有关系的
UPDATE OUTPUT into a variable
Because an update can affect multiple rows, it requires a table to store its results:
declare @ids table (id int);
UPDATE Foo
SET Bar = 1
OUTPUT INSERTED.Id INTO @ids
WHERE Baz = 2If you're sure only one row will be affected, you can pull out the id like:
declare @id int
select top 1 @id = id
from @ids