在SQL Server中，执行Insert时是否可以获取记录的“id”？

In SQL Server 2005 I have an "id" field in a table that has the "Is Identity" property set to 'Yes'. So, when an Insert is executed on that table the "id" gets set automatically to the next incrementing integer. Is there an easy way when the Insert is executed to get what the "id" was set to without having to do a Select statement right after the Insert?

在SQL Server 2005中，我在“Is Identity”属性设置为“Yes”的表中有一个“id”字段。因此，当在该表上执行Insert时，“id”将自动设置为下一个递增整数。执行Insert是否有一种简单的方法来获取“id”的设置，而不必在Insert之后立即执行Select语句？

duplicate of:
Best way to get identity of inserted row?

重复：获取插入行的标识的最佳方法？

7 个解决方案

#1

In .Net at least, you can send multiple queries to the server in one go. I do this in my app:

至少在.Net中，您可以一次性向服务器发送多个查询。我在我的应用程序中这样做：

command.CommandText = "INSERT INTO [Employee] (Name) VALUES (@Name); SELECT SCOPE_IDENTITY()";
int id = (int)command.ExecuteScalar();

Works like a charm.

奇迹般有效。

#2

If you're inserting multiple rows, the use of the OUTPUT and INSERTED.columnname clause on the insert statement is a simple way of getting all the ids into a temp table.

如果要插入多行，则在insert语句中使用OUTPUT和INSERTED.columnname子句是将所有id都放入临时表的简单方法。

DECLARE @MyTableVar table( ID int,  
                               Name varchar(50),  
                               ModifiedDate datetime);  
INSERT MyTable  
        OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar  
SELECT someName, GetDate() from SomeTable

#3

Scope_identity() is the preferred way, see: 6 Different Ways To Get The Current Identity Value

Scope_identity（）是首选方式，请参阅：获取当前标识值的6种不同方法

#4

SCOPE_IDENTITY(); is your best bet. And if you are using .NET just pass an our parameter and check the value after the procedure is run.

SCOPE_IDENTITY（）;是你最好的选择。如果您使用的是.NET，只需传递一个参数并在程序运行后检查该值。

CREATE PROCEDURE [dbo].[InsertProducts]
    @id             INT             = NULL OUT,
    @name           VARCHAR(150)    = NULL,
    @desc           VARCHAR(250)    = NULL

AS

    INSERT INTO dbo.Products
       (Name,
        Description)
    VALUES
       (@name,
        @desc)

    SET @id = SCOPE_IDENTITY();

#5

You have to select the scope_identity() function.

您必须选择scope_identity（）函数。

To do this from application code, I normally encapsulate this process in a stored procedure so it still looks like one query to my application.

为了从应用程序代码执行此操作，我通常将此进程封装在存储过程中，因此它仍然看起来像是对我的应用程序的一个查询。

#6

I tend to prefer attaching a trigger to the table using enterprise manager. That way you don't need to worry about writing out extra sql statements in your code. Mine look something like this:

我倾向于使用企业管理器将触发器附加到表中。这样您就不必担心在代码中写出额外的sql语句了。我看起来像这样：

Create Trigger tblName On dbo.tblName For Insert As select new_id = @@IDENTITY

在dbo.tblName上创建触发器tblName对于Insert As，选择new_id = @@ IDENTITY

Then, from within your code, treat your insert statements like select statements- Just execute and evaluate the results. the "newID" column will contain the identity of the row you just created.

然后，从代码中，将insert语句视为select语句 - 只需执行并评估结果即可。 “newID”列将包含您刚刚创建的行的标识。

#7

This is probably the best working solution I found for SQL Server.. Sql Server return the value of identity column after insert statement

这可能是我为SQL Server找到的最佳工作解决方案.Sql Server在insert语句后返回identity列的值

#1