Https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide
- The function returns the last identity of the created in the
@@identity
same session.
- The
scope_identity()
function returns the last identity created in the same session and the same scope.
- The last identity of the
ident_current(name)
returns created for a specific table or view in any session.
identity()
The function is not used to get an identity, it's used to create an identity in a select...into
query.
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where scope_identity()
@@identity
the and the functions differ are if you had a trigger on the table. If you had a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity()
function would Return the identity created by the query, while the @@identity
function would return the identity created by the trigger.
So, normally your would use the scope_identity()
function.
Https://stackoverflow.com/questions/13399565/is-there-any-way-to-use-scope-identity-if-using-a-multiple-insert-statement
No, only SCOPE_IDENTITY()
gives the one , latest inserted IDENTITY
value. Could check out the clause of OUTPUT
SQL Server ....
DECLARE @IdentityTable TABLE(SomekeyvalueINT, newidentityINT)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 would hold "some key value" (for your, to identify the row) and the Newly inserted ID
values for each row inserted. Now go crazy with this! :-)
where inserted is related to output.
Identity in SQL Server