對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2019/7/29 13:27:42
How to get the value of autoincrement of last row at the insert
I have googled this problem one week and no thing useful I think am not using the correct word
I am using SQL Server 2008 with t-sql and my need is to optimise my function when I insert a new row.
I have a table with first column is the key of integer autoincrement type and other columns are just for information
When we do an insert, SQL Server increments the key automatically and I have to do a select max to get the value, so is there a way like a global variable like @@IDENTITY or a function to avoid the begin end transaction and select max
Use SCOPE_IDENTITY:
Which will give you:
The last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Just ran the code:
and it also works!
What about this for last auto increment value
If you are using MySQL you get the auto increment ID of the last insert with:
In my case I had to use @@Identity, because I was inserting into a view. It seems that SCOPE_IDENTITY only works for ones you have explicitly created.
See here:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
@@IDENTITY will return the last identity value entered into a table in your current session.
While @@IDENTITY is limited to the current session, it is not limited to the current scope.
If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
原文出處： sql - How to get the value of autoincrement of last row at the insert - Stack Overflow
I have googled this problem one week and no thing useful I think am not using the correct word
I am using SQL Server 2008 with t-sql and my need is to optimise my function when I insert a new row.
I have a table with first column is the key of integer autoincrement type and other columns are just for information
When we do an insert, SQL Server increments the key automatically and I have to do a select max to get the value, so is there a way like a global variable like @@IDENTITY or a function to avoid the begin end transaction and select max
Use SCOPE_IDENTITY:
-- do insert
SELECT SCOPE_IDENTITY();
Which will give you:
The last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Just ran the code:
INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
and it also works!
What about this for last auto increment value
SELECT IDENT_CURRENT('tableName')-IDENT_INCR('tableName');
If you are using MySQL you get the auto increment ID of the last insert with:
SELECT LAST_INSERT_ID();
In my case I had to use @@Identity, because I was inserting into a view. It seems that SCOPE_IDENTITY only works for ones you have explicitly created.
See here:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
@@IDENTITY will return the last identity value entered into a table in your current session.
While @@IDENTITY is limited to the current session, it is not limited to the current scope.
If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
原文出處： sql - How to get the value of autoincrement of last row at the insert - Stack Overflow