Get the ID of the most recently inserted data

In SQL Server 2000, insert data returns an AutoNumber ID, and there are three ways to implement Scope_identity, Ident_current, and @ @IDENTITY, all of which return values inserted into the IDENTITY column.

Ident_current: Returns the identity value that was last generated for any session and for a particular table in any scope. Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current returns the value generated for a specific table in any session and scope.

@ @IDENTITY: Returns the identity value that was last generated for any table in all scopes of the current session.

Scope_identity: Returns the last identity value generated for the current session and any tables in the current scope

The scope_identity and @ @IDENTITY return the last identity value generated within any table in the current session. However, scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

Examples of Use:
Set @NewID =scope_identity ()
Set @[email protected] @IDENTITY
Set @NewID =ident_current (' Table name ')
For example, there are two tables T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted into the T1, the trigger is fired and a row is inserted in the T2. This example illustrates two scopes: one is an insert on T1 and the other is an insert on T2 as the result of a trigger. Assuming that both T1 and T2 have an IDENTITY column, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1. @ @IDENTITY returns the last IDENTITY column value inserted into any scope in the current session, which is the value inserted into the T2. Scope_identity () returns the IDENTITY value in the Insert T1, which is the last insert that occurs in the same scope. The function returns a NULL value if the scope_identity () function is invoked before the INSERT statement to the IDENTITY column occurs in the scope. The values returned by Ident_current (' T1 ') and ident_current (' T2 ') are the last self-increment values of the two tables respectively.

The first method:
INSERT INTO table1 values (colvalue1,colvalue2)

Select Ident_current (' table1 ')

The second method:
INSERT INTO table1 values (colvalue1,colvalue2)
SELECT @ @identity

Main application of functions in Transact-SQL

1, Ident_current ()

2. SQL Server 2000 identity function scope_identity ()

3. SQL Server 2000 identity function @ @IDENTITY

Ident_current ()

Returns the most recent identity value generated for a table or view specified in a session and scope.

Grammar

Ident_current (' table_name ')

Notes

Ident_current is similar to the SQL Server 2000 identity function scope_identity and @ @IDENTITY. All three functions return the last generated identity value. However, the "last" scope and the session defined in each of these functions are different.

• Ident_current returns the most recent identity value generated for a session and the specified table in the domain.
  
  
• @ @IDENTITY Returns the most recent identity value generated for a table in the current session across all scopes.
  
  
• Scope_identity returns the most recent identity value generated for the current session and a table in the current scope.
  
  

When you call the Ident_current function in an empty table, this function returns NULL.

If statements and transactions fail, they change the current identity of the table, causing the values in the identity column to become incoherent. Even if you do not commit a transaction that attempts to insert a value into the table, you can never roll back the identity value. For example, if an INSERT statement fails due to a ignore_dup_key conflict, the current identity value of the table will still increase.

Parameters

table_name

The name of the table whose identity value is returned. The data type of table_name is varchar and has no default value.

return type

sql_variant

Example

The following example displays the IDENT_CURRENT @@IDENTITY SCOPE_IDENTITY different identity values that are represented by, and returned from.

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 ()

Returns the last identity value in an identity column that is inserted into the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Grammar

Scope_identity ()

Notes

Scope_identity, Ident_current, and @ @IDENTITY are similar functions because they all return values that are inserted into the identity column.

Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current returns the value generated for a specific table in any session and scope. For more information, see ident_current (Transact-SQL).

The scope_identity and @ @IDENTITY return the last identity value generated within any table in the current session. However, scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

For example, there are two tables T1 and T2, and INSERT triggers are defined on T1. When a row is inserted into the T1, the trigger is fired and a row is inserted in the T2. This scenario demonstrates two scopes: inserts on T1, and inserts in T2 through triggers.

Assuming that both T1 and T2 have identity columns, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1. @ @IDENTITY Returns the value of the last identity column inserted within any scope in the current session. This is the value inserted in the T2. Scope_identity () returns the IDENTITY value that is inserted in the T1. This is the last insert that occurs within the same scope. If the scope_identity () function is called before any INSERT statement acts on the identity column in the scope, the function returns a null value.

If statements and transactions fail, they change the current identity of the table, causing the values in the identity column to become incoherent. Even if you do not commit a transaction that attempts to insert a value into the table, you can never roll back the identity value. For example, if an INSERT statement fails due to a ignore_dup_key conflict, the current identity value of the table will still increase.

return type

Numeric

Example

The following columns create two tables, TZ and TY then TZ create an INSERT trigger in. When a row is inserted into a table TZ , the trigger ( Ztrig ) fires and inserts a row in it TY .

 

use tempdb GO CREATE TABLE TZ (z_id int IDENTITY () PRIMARY K EY, Z_name varchar () not NULL) Insert TZ values (' Lisa ') Insert TZ values (' Mike ') Insert TZ values (' Carla ') SELECT * F ROM TZ--result set:this is the 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 ') S Elect * from TY--result set:this was how Ty looks:y_id y_name---------------boathouse, rocks, elevator creat E TRIGGER Ztrig on TZ for insert as BEGIN insert TY values (") END insert TZ values (' Rosalie ') SELECT scope_identity () A S [scope_identity] Go SELECT @ @IDENTITY as [@ @IDENTITY] Go

Here is the result set:

Scope_identity 4 @ @IDENTITY 115

@ @IDENTITY

Returns the system function for the last inserted identity value.

Grammar

@ @IDENTITY

Notes

After an INSERT, SELECT into, or bulk copy statement is complete, the @ @IDENTITY contains the last identity value generated by the statement. If the statement does not affect any table that contains an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted and multiple identity values are generated, the @ @IDENTITY returns the last generated identity value. If the statement triggers one or more triggers, and the trigger performs an insert operation that generates an identity value, then the @ @IDENTITY is called immediately after the statement executes to return the last identity value generated by the trigger. If a trigger is triggered after an insert operation on a table that contains an identity column, and the trigger performs an insert operation on another table that does not have an identity column, the @ @IDENTITY returns the identity value that was first inserted. When an INSERT or SELECT into statement fails or a bulk copy fails, or if a transaction is rolled back, the @ @IDENTITY value does not revert to the previous setting.

If statements and transactions fail, they change the current identity of the table, causing the values in the identity column to become incoherent. Even if you do not commit a transaction that attempts to insert a value into the table, you can never roll back the identity value. For example, if an INSERT statement fails due to a ignore_dup_key conflict, the current identity value of the table will still increase.

@ @IDENTITY, scope_identity, and ident_current are similar functions because they all return the last value inserted into the IDENTITY column of the table.

@ @IDENTITY and scope_identity can return the last identity value generated in all tables in the current session. However, scope_identity only returns values within the current scope, while the @ @IDENTITY is not limited to a specific scope.

Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current can return identity values generated for a particular table in any session and in any scope. For more information, see ident_current (Transact-SQL).

The @ @IDENTITY function is scoped to the current session on the local server where the function is executed. This function cannot be applied to a remote or linked server. To obtain an identity value on another server, execute the stored procedure on the remote server or linked server and make the stored procedure (executed in the environment of a remote or linked server) collect the identity value and return it to the calling connection on the local server.

return type

Numeric

Example

The following example inserts a row into the table that contains the identity column ( LocationID ) and uses @@IDENTITY the identity value that is used to display the new row.

Reference: http://www.cnblogs.com/hnNet/articles/1455071.html

Get the ID of the most recently inserted data (GO)