SQL Server - 插入后的返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7917695/
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
SQL Server - Return value after INSERT
提问by melbic
I'm trying to get a the key-value back after an INSERT-statement. Example: I've got a table with the attributes name and id. id is a generated value.
我试图在 INSERT 语句后取回键值。示例：我有一个带有属性名称和 ID 的表。id 是生成的值。
INSERT INTO table (name) VALUES('bob');
Now I want to get the id back in the same step. How is this done?
现在我想在同一步骤中取回 id。这是怎么做的？
We're using Microsoft SQL Server 2008.
我们使用的是 Microsoft SQL Server 2008。
回答by gbn
No need for a separate SELECT...
不需要单独的 SELECT ...
INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');
This works for non-IDENTITY columns (such as GUIDs) too
这也适用于非 IDENTITY 列（例如 GUID）
回答by Curt
Use SCOPE_IDENTITY()to get the new ID value
使用SCOPE_IDENTITY()以获取新的ID值
INSERT INTO table (name) VALUES('bob');
SELECT SCOPE_IDENTITY()
回答by hajikelist
INSERT INTO files (title) VALUES ('whatever');
SELECT * FROM files WHERE id = SCOPE_IDENTITY();
Is the safest bet since there is a known issue with OUTPUT Clause conflict on tables with triggers. Makes this quite unreliable as even if your table doesn't currently have any triggers - someone adding one down the line will break your application. Time Bomb sort of behaviour.
是最安全的选择，因为在带有触发器的表上存在 OUTPUT 子句冲突的已知问题。使这非常不可靠，即使您的表当前没有任何触发器 - 有人添加一个会破坏您的应用程序。定时炸弹的行为。
See msdn article for deeper explanation:
请参阅 msdn 文章以获得更深入的解释：
回答by Ian Boyd
Entity Framework performs something similar to gbn's answer:
实体框架执行类似于 gbn 的回答：
DECLARE @generated_keys table([Id] uniqueidentifier)
INSERT INTO Customers(FirstName)
OUTPUT inserted.CustomerID INTO @generated_keys
VALUES('bob');
SELECT t.[CustomerID]
FROM @generated_keys AS g
JOIN dbo.Customers AS t
ON g.Id = t.CustomerID
WHERE @@ROWCOUNT > 0
The output results are stored in a temporary table variable, and then selected back to the client. Have to be aware of the gotcha:
输出结果存储在临时表变量中，然后选择返回给客户端。必须注意陷阱：
inserts can generate more than one row, so the variable can hold more than one row, so you can be returned more than one
ID
inserts 可以生成不止一行，所以变量可以容纳不止一行，所以可以返回不止一行
ID
I have no idea why EF would inner join the ephemeral table back to the real table (under what circumstances would the two not match).
我不知道为什么 EF 会将临时表内部连接回真实表（在什么情况下两者不匹配）。
But that's what EF does.
但这就是EF所做的。
SQL Server 2008 or newer only. If it's 2005 then you're out of luck.
仅限 SQL Server 2008 或更新版本。如果是 2005 年，那你就不走运了。
回答by AngelaG
@@IDENTITYIs a system function that returns the last-inserted identity value.
@@IDENTITY是一个系统函数，它返回最后插入的标识值。
回答by Reza Jenabi
There are many ways to exit after insert
插入后退出的方式有很多种
When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that's been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application. Use the OUTPUT INTO form if you want to return the data to a table or a table variable.
向表中插入数据时，可以使用 OUTPUT 子句返回已插入表中的数据的副本。OUTPUT 子句采用两种基本形式：OUTPUT 和 OUTPUT INTO。如果要将数据返回到调用应用程序，请使用 OUTPUT 表单。如果要将数据返回到表或表变量，请使用 OUTPUT INTO 表单。
DECLARE @MyTableVar TABLE (id INT,NAME NVARCHAR(50));
INSERT INTO tableName
(
NAME,....
)OUTPUT INSERTED.id,INSERTED.Name INTO @MyTableVar
VALUES
(
'test',...
)
IDENT_CURRENT: It returns the last identity created for a particular table or view in any session.
IDENT_CURRENT：它返回为任何会话中的特定表或视图创建的最后一个标识。
SELECT IDENT_CURRENT('tableName') AS [IDENT_CURRENT]
SCOPE_IDENTITY: It returns the last identity from a same session and the same scope. A scope is a stored procedure/trigger etc.
SCOPE_IDENTITY：它返回来自同一会话和同一范围的最后一个身份。范围是存储过程/触发器等。
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
@@IDENTITY: It returns the last identity from the same session.
@@IDENTITY：它返回同一会话的最后一个身份。
SELECT @@IDENTITY AS [@@IDENTITY];
回答by MNF
The best and most sure solution is using SCOPE_IDENTITY().
最好和最确定的解决方案是使用SCOPE_IDENTITY().
Just you have to get the scope identity after every insert and save it in a variable because you can call two insert in the same scope.
只是您必须在每次插入后获取范围标识并将其保存在变量中，因为您可以在同一范围内调用两次插入。
ident_currentand @@identitymay be they work but they are not safe scope. You can have issues in a big application
ident_current并且@@identity可能它们有效，但它们不是安全范围。您可能会在大型应用程序中遇到问题
declare @duplicataId int
select @duplicataId = (SELECT SCOPE_IDENTITY())
More detail is here Microsoft docs
更多细节在这里Microsoft docs
回答by Purplegoldfish
You can use scope_identity()to select the ID of the row you just inserted into a variable then just select whatever columns you want from that table where the id = the identity you got from scope_identity()
您可以使用scope_identity()来选择刚刚插入变量的行的 ID，然后从该表中选择您想要的任何列，其中 id = 您从中获得的身份scope_identity()
See here for the MSDN info http://msdn.microsoft.com/en-us/library/ms190315.aspx
有关 MSDN 信息，请参见此处http://msdn.microsoft.com/en-us/library/ms190315.aspx
回答by Richard Zembron
This is how I use OUTPUT INSERTED, when inserting to a table that uses ID as identity column in SQL Server:
这就是我在插入到使用 ID 作为 SQL Server 中的标识列的表时使用 OUTPUT INSERTED 的方式：
'myConn is the ADO connection, RS a recordset and ID an integer
Set RS=myConn.Execute("INSERT INTO M2_VOTELIST(PRODUCER_ID,TITLE,TIMEU) OUTPUT INSERTED.ID VALUES ('Gator','Test',GETDATE())")
ID=RS(0)
回答by Robert Quinn
You can append a select statement to your insert statement. Integer myInt = Insert into table1 (FName) values('Fred'); Select Scope_Identity(); This will return a value of the identity when executed scaler.
您可以将选择语句附加到插入语句。Integer myInt = Insert into table1 (FName) values('Fred'); 选择 Scope_Identity(); 这将在执行缩放器时返回标识的值。
