SQLServer 如何擷取剛插入資料的Id號

第一種方法：
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;GODROP TABLE t6;DROP TABLE t7;GOCREATE TABLE t6(id int IDENTITY);CREATE TABLE t7(id int IDENTITY(100,1));GOCREATE TRIGGER t6ins ON t6 FOR INSERT ASBEGIN INSERT t7 DEFAULT VALUESEND;GO--End of trigger definitionSELECT * FROM t6;--id is empty.SELECT * FROM t7;--ID is empty.--Do the following in Session 1INSERT t6 DEFAULT VALUES;SELECT @@IDENTITY;/*Returns the value 100. This was inserted by the trigger.*/SELECT SCOPE_IDENTITY();/* Returns the value 1. This was inserted by the INSERT statement two statements before this query.*/SELECT IDENT_CURRENT('t7');/* Returns value inserted into t7, that is in the trigger.*/SELECT IDENT_CURRENT('t6');/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/-- Do the following in Session 2.SELECT @@IDENTITY;/* Returns NULL because there has been no INSERT action up to this point in this session.*/SELECT SCOPE_IDENTITY();/* Returns NULL because there has been no INSERT action up to this point in this scope in this session.*/SELECT IDENT_CURRENT('t7');/* Returns the last value inserted into 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() 函數，則該函數將返回空值。

如果語句和事務失敗，它們會更改表的當前標識，從而使識別欄位中的值出現不連貫現象。即使未提交試圖向表中插入值的事務，也永遠無法復原標識值。例如，如果因 IGNORE_DUP_KEY 衝突而導致 INSERT 語句失敗，表的當前標識值仍然會增加。

傳回型別

numeric

樣本

以下示列建立兩個表，TZ 和 TY，並在 TZ 中建立一個 INSERT 觸發器。當將某行插入表 TZ 中時，觸發器 (Ztrig) 將激發並在 TY 中插入一行。

USE tempdbGOCREATE 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 Lisa2 Mike3 CarlaCREATE 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 boathouse105 rocks110 elevator/*Create the trigger that inserts a row in table TY when a row is inserted in table TZ*/CREATE TRIGGER ZtrigON TZFOR INSERT AS BEGIN INSERT TY VALUES ('') END/*FIRE the trigger and determine what identity values you obtain with the @@IDENTITY and SCOPE_IDENTITY functions.*/INSERT TZ VALUES ('Rosalie')SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]GOSELECT @@IDENTITY AS [@@IDENTITY]GO

下面是結果集： 

SCOPE_IDENTITY4/*SCOPE_IDENTITY returned the last identity value in the same scope. This was the insert on table TZ.*/@@IDENTITY115/*@@IDENTITY returned the last identity value inserted to TY by the trigger. This fired because of an earlier insert on TZ.*/

 

@@IDENTITY

 

返回最後插入的標識值的系統函數。

 

文法

@@IDENTITY

備忘

在一條 INSERT、SELECT INTO 或大量複製陳述式完成後，@@IDENTITY 中包含語句產生的最後一個標識值。如果語句未影響任何包含識別欄位的表，則 @@IDENTITY 返回 NULL。如果插入了多個行，產生了多個標識值，則 @@IDENTITY 將返回最後產生的標識值。如果語句觸發了一個或多個觸發器，該觸發器又執行了產生標識值的插入操作，那麼，在語句執行後立即調用 @@IDENTITY 將返回觸發器產生的最後一個標識值。如果對包含識別欄位的表執行插入操作後觸發了觸發器，並且觸發器對另一個沒有識別欄位的表執行了插入操作，則 @@IDENTITY 將返回第一次插入的標識值。出現 INSERT 或 SELECT INTO 語句失敗或大量複製失敗，或者事務被復原的情況時，@@IDENTITY 值不會恢複為以前的設定。

如果語句和事務失敗，它們會更改表的當前標識，從而使識別欄位中的值出現不連貫現象。即使未提交試圖向表中插入值的事務，也永遠無法復原標識值。例如，如果因 IGNORE_DUP_KEY 衝突而導致 INSERT 語句失敗，表的當前標識值仍然會增加。

@@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;GOINSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)VALUES ('Damaged Goods', 5, 2.5, GETDATE());GOSELECT @@IDENTITY AS 'Identity';GO--Display the value of LocationID of the newly inserted row.SELECT MAX(LocationID) FROM Production.Location;GO