identity in sql server 批量插入history

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有关系的

 

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 = 2

If 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

 

 

 

 

 

 

 

作者:Chuck Lu    GitHub