SQL Server 获取刚插入数据的ID号

最新推荐文章于 2022-11-07 22:33:49 发布

转载最新推荐文章于 2022-11-07 22:33:49 发布 · 3.3k 阅读

文章标签：

#SQL Server #od #数据库

本文介绍了在SQL Server中获取标识值的三种方法：IDENT_CURRENT、SCOPE_IDENTITY和@@IDENTITY，包括它们的区别、应用场景及示例。

第一种方法：
insert into table1 values(colvalue1,colvalue2)

select ident_current('table1')

第二种方法：
insert into table1 values(colvalue1,colvalue2)
select @@identity

主要应用Transact-SQL中的函数

1、IDENT_CURRENT（）

2、SQL Server 2000 标识函数 SCOPE_IDENTITY （）

3、SQL Server 2000 标识函数 @@IDENTITY

IDENT_CURRENT（）

返回为某个会话和作用域中指定的表或视图生成的最新的标识值。

语法


IDENT_CURRENT( 'table_name' )

备注

IDENT_CURRENT 类似于 SQL Server 2000 标识函数 SCOPE_IDENTITY 和 @@IDENTITY。这三个函数都返回最后生成的标识值。但是，上述每个函数中定义的“最后”的作用域和会话有所不同。

IDENT_CURRENT 返回为某个会话和用域中的指定表生成的最新标识值。
@@IDENTITY 返回为跨所有作用域的当前会话中的某个表生成的最新标识值。
SCOPE_IDENTITY 返回为当前会话和当前作用域中的某个表生成的最新标识值。

在空表中调用 IDENT_CURRENT 函数时，此函数将返回 NULL。

如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

参数

table_name

其标识值被返回的表的名称。table_name 的数据类型为 varchar，无默认值。

返回类型

sql_variant

示例

以下示例将显示由 IDENT_CURRENT、@@IDENTITY 和 SCOPE_IDENTITY 返回的不同标识值。


USE AdventureWorks; GO DROP TABLE t6; DROP TABLE t7; GO CREATE TABLE t6(id int IDENTITY); CREATE TABLE t7(id int IDENTITY(100,1)); GO CREATE TRIGGER t6ins ON t6 FOR INSERT AS BEGIN INSERT t7 DEFAULT VALUES END; GO --End of trigger definition SELECT * FROM t6; --id is empty. SELECT * FROM t7; --ID is empty. --Do the following in Session 1 INSERT t6 DEFAULT VALUES; SELECT @@IDENTITY; SELECT SCOPE_IDENTITY(); SELECT IDENT_CURRENT('t7'); SELECT IDENT_CURRENT('t6'); -- Do the following in Session 2. SELECT @@IDENTITY; SELECT SCOPE_IDENTITY(); SELECT IDENT_CURRENT('t7');

USE
 AdventureWorks; GO DROP TABLE t6; DROP TABLE t7; GO CREATE TABLE t6(id 
int IDENTITY); CREATE TABLE t7(id int IDENTITY(100,1)); GO CREATE 
TRIGGER t6ins ON t6 FOR INSERT AS BEGIN INSERT t7 DEFAULT VALUES END; GO
 --End of trigger definition SELECT * FROM t6; --id is empty. SELECT * 
FROM t7; --ID is empty. --Do the following in Session 1 INSERT t6 
DEFAULT VALUES; SELECT @@IDENTITY; SELECT SCOPE_IDENTITY(); SELECT 
IDENT_CURRENT('t7'); SELECT IDENT_CURRENT('t6'); -- Do the following in 
Session 2. SELECT @@IDENTITY; SELECT SCOPE_IDENTITY(); SELECT 
IDENT_CURRENT('t7');

SCOPE_IDENTITY （）

返回插入到同一作用域中的标识列内的最后一个标识值。一个范围是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。

语法


SCOPE_IDENTITY()

备注

SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 是相似的函数，因为它们都返回插入到标识列中的值。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。有关详细信息，请参阅 IDENT_CURRENT (Transact-SQL)。

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。

例如，有两个表 T1 和 T2，并且在 T1 上定义了 INSERT 触发器。当将某行插入 T1 时，触发器被激发，并在 T2 中插入一行。该方案演示了两个作用域：在 T1 上的插入，以及在 T2 通过触发器的插入。

假设 T1 和 T2 都有标识列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。@@IDENTITY 将返回在当前会话中的任何作用域内插入的最后一个标识列的值。这是在 T2 中插入的值。SCOPE_IDENTITY() 将返回在 T1 中插入的 IDENTITY 值。这是在同一个作用域内发生的最后的插入。如果在任何 INSERT 语句作用于作用域中的标识列之前调用 SCOPE_IDENTITY() 函数，则该函数将返回空值。

返回类型

numeric

示例

以下示列创建两个表，TZ 和 TY，并在 TZ 中创建一个 INSERT 触发器。当将某行插入表 TZ 中时，触发器 (Ztrig) 将激发并在 TY 中插入一行。


USE tempdb GO CREATE TABLE TZ ( Z_id int IDENTITY(1,1)PRIMARY KEY, Z_name varchar(20) NOT NULL) INSERT TZ VALUES ('Lisa') INSERT TZ VALUES ('Mike') INSERT TZ VALUES ('Carla') SELECT * FROM TZ --Result set: This is how table TZ looks. Z_id Z_name ------------- 1 Lisa 2 Mike 3 Carla CREATE TABLE TY ( Y_id int IDENTITY(100,5)PRIMARY KEY, Y_name varchar(20) NULL) INSERT TY (Y_name) VALUES ('boathouse') INSERT TY (Y_name) VALUES ('rocks') INSERT TY (Y_name) VALUES ('elevator') SELECT * FROM TY --Result set: This is how TY looks: Y_id Y_name --------------- 100 boathouse 105 rocks 110 elevator CREATE TRIGGER Ztrig ON TZ FOR INSERT AS BEGIN INSERT TY VALUES ('') END INSERT TZ VALUES ('Rosalie') SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] GO SELECT @@IDENTITY AS [@@IDENTITY] GO

USE
 tempdb GO CREATE TABLE TZ ( Z_id int IDENTITY(1,1)PRIMARY KEY, Z_name 
varchar(20) NOT NULL) INSERT TZ VALUES ('Lisa') INSERT TZ VALUES 
('Mike') INSERT TZ VALUES ('Carla') SELECT * FROM TZ --Result set: This 
is how table TZ looks. Z_id Z_name ------------- 1 Lisa 2 Mike 3 Carla 
CREATE TABLE TY ( Y_id int IDENTITY(100,5)PRIMARY KEY, Y_name 
varchar(20) NULL) INSERT TY (Y_name) VALUES ('boathouse') INSERT TY 
(Y_name) VALUES ('rocks') INSERT TY (Y_name) VALUES ('elevator') SELECT *
 FROM TY --Result set: This is how TY looks: Y_id Y_name ---------------
 100 boathouse 105 rocks 110 elevator CREATE TRIGGER Ztrig ON TZ FOR 
INSERT AS BEGIN INSERT TY VALUES ('') END INSERT TZ VALUES ('Rosalie') 
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] GO SELECT @@IDENTITY AS 
[@@IDENTITY] GO

下面是结果集：


SCOPE_IDENTITY 4 @@IDENTITY 115

@@IDENTITY

返回最后插入的标识值的系统函数。

语法


@@IDENTITY

备注

在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含语句生成的最后一个标识值。如果语句未影响任何包含标识列的表，则 @@IDENTITY 返回 NULL。如果插入了多个行，生成了多个标识值，则 @@IDENTITY 将返回最后生成的标识值。如果语句触发了一个或多个触发器，该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。如果对包含标识列的表执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表执行了插入操作，则 @@IDENTITY 将返回第一次插入的标识值。出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY 值不会恢复为以前的设置。

@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 是相似的函数，因为他们都返回插入到表的 IDENTITY 列的最后一个值。

@@IDENTITY 和 SCOPE_IDENTITY 可以返回当前会话中的所有表中生成的最后一个标识值。但是，SCOPE_IDENTITY 只在当前作用域内返回值，而 @@IDENTITY 不限于特定的作用域。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 可以返回任何会话和任何作用域中为特定表生成的标识值。有关详细信息，请参阅 IDENT_CURRENT (Transact-SQL)。

@@IDENTITY 函数的作用域是执行该函数的本地服务器上的当前会话。此函数不能应用于远程或链接服务器。若要获得其他服务器上的标识值，请在远程服务器或链接服务器上执行存储过程，并使（在远程或链接服务器的环境中执行的）该存储过程收集标识值，并将其返回本地服务器上的发出调用的连接。

返回类型

numeric

示例

以下示例向包含标识列 (LocationID) 的表中插入一行，并使用 @@IDENTITY 显示新行中使用的标识值。


USE AdventureWorks; GO --Display the value of LocationID in the last row in the table. SELECT MAX(LocationID) FROM Production.Location; GO INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate) VALUES ('Damaged Goods', 5, 2.5, GETDATE()); GO SELECT @@IDENTITY AS 'Identity'; GO --Display the value of LocationID of the newly inserted row. SELECT MAX(LocationID) FROM Production.Location; GO

USE
 AdventureWorks; GO --Display the value of LocationID in the last row in
 the table. SELECT MAX(LocationID) FROM Production.Location; GO INSERT 
INTO Production.Location (Name, CostRate, Availability, ModifiedDate) 
VALUES ('Damaged Goods', 5, 2.5, GETDATE()); GO SELECT @@IDENTITY AS 
'Identity'; GO --Display the value of LocationID of the newly inserted 
row. SELECT MAX(LocationID) FROM Production.Location; GO