identity in sql server

標籤：overflow   microsoft   specific   diff   lan   and   select   sql   proc   

https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide

• The @@identity function 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 a select...into query.

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.

 

https://stackoverflow.com/questions/13399565/is-there-any-way-to-use-scope-identity-if-using-a-multiple-insert-statement

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有關係的

 

 

 

 

identity in sql server