获取最后插入的行ID(用SQL语句) [英] Get the last inserted row ID (with SQL statement)

问题描述

我想在表中插入新记录时获取新创建的 ID.

I want to get the new created ID when you insert a new record in table.

我读过这个:http://msdn.microsoft.com/en-us/library/ms177564.aspx 但需要创建临时表.

I read this: http://msdn.microsoft.com/en-us/library/ms177564.aspx but it needs to create temporary table.

我想在执行 INSERT 语句后返回 ID(假设只执行一个 INSERT).

I want to return the ID after executing INSERT statement (assuming executing just one INSERT).

示例:

1     Joe      Joe
2     Michael  Mike
3     Zoe      Zoe

在执行 INSERT 语句时，我想返回创建的 ID，意思是 4.

When executing an INSERT statement, I want to return the created ID, means 4.

能否告诉我如何使用 SQL 语句来做到这一点，否则不可能?

Can tell me how to do that using SQL statement or it is not possible ?

推荐答案

如果您的 SQL Server 表具有 INT IDENTITY(或 BIGINT IDENTITY)类型的列，则您可以使用以下方法获取最新插入的值:

If your SQL Server table has a column of type INT IDENTITY (or BIGINT IDENTITY), then you can get the latest inserted value using:

INSERT INTO dbo.YourTable(columns....)
   VALUES(..........)

SELECT SCOPE_IDENTITY()

只要您没有插入另一行，它就可以工作 - 它只返回在此范围内分发的最后一个 IDENTITY 值.

This works as long as you haven't inserted another row - it just returns the last IDENTITY value handed out in this scope here.

至少还有两个选项 - @@IDENTITY 和 IDENT_CURRENT - 详细了解它们的工作原理以及它们的不同之处(并且可能会给您意外结果)在这个 这里是 Pinal Dave 的精彩博文.

There are at least two more options - @@IDENTITY and IDENT_CURRENT - read more about how they works and in what way they're different (and might give you unexpected results) in this excellent blog post by Pinal Dave here.

这篇关于获取最后插入的行ID(用SQL语句)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！