SQL 如何在插入时获取最后一行的自动增量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13451070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the value of autoincrement of last row at the insert
提问by yacine ouah
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.
我正在使用带有 t-sql 的 SQL Server 2008，我需要在插入新行时优化我的函数。
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 @@IDENTITYor a function to avoid the begin end transaction and select max
当我们进行插入时，SQL Server 会自动增加键，我必须执行 select max 才能获取值，所以有没有像全局变量这样的方法@@IDENTITY或函数来避免开始结束事务并选择 max
回答by Mahmoud Gamal
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.
插入到同一范围内的标识列中的最后一个标识值。作用域是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句在同一个存储过程、函数或批处理中，则它们在同一范围内。
回答by Eddy Jawed
Just ran the code:
刚刚运行了代码：
INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
and it also works!
它也有效！
回答by user1810132
What about this for last auto increment value
这个最后的自动增量值怎么样
SELECT IDENT_CURRENT('tableName')-IDENT_INCR('tableName');
回答by Erik Kalkoken
回答by Dan
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.
就我而言，我不得不使用@@Identity，因为我要插入到视图中。似乎 SCOPE_IDENTITY 仅适用于您明确创建的那些。
See here:
看这里：
@@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.
@@IDENTITY 将返回在当前会话中输入到表中的最后一个标识值。虽然@@IDENTITY 仅限于当前会话，但不限于当前范围。如果表上的触发器导致在另一个表中创建标识，您将获得最后创建的标识，即使它是创建它的触发器。
