Select @ identity usage

Use select @ identity to obtain the ID automatically generated when the last record is inserted.

If you use a stored procedure, the Code is as follows: Set @ [email protected] @ identity

Note:

After an insert, select into, or large-capacity copy statement is completed, @ identity contains the final id value generated by this statement. If this statement does not affect any table with an ID column, @ identity returns NULL. If multiple rows are inserted, multiple ID values are generated. @ identity returns the final id value. If one or more triggers are triggered to execute the insert operation that generates the id value, @ identity is called immediately after the statement is executed, and the last id value generated by the trigger is returned. If the insert or select into statement fails, the large-capacity copy fails, or the transaction is rolled back, the @ identity value is not restored to the previous setting.

The functions of @ identity, scope_identity, and ident_current are similar in returning the last value of the @ identity column inserted to the table.

@ Identity and scope_identity return the last id value generated in all tables of the current session. However, scope_identity only returns values within the current scope, and @ identity is not limited to specific scopes.

Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the id value generated for a specific table in any session and in any scope. For more information, see ident_current.

Example

The following example inserts a row into a table with an ID column and uses @ identity to display the id value used in the new row.

Insert into infoclass (infoclass) values ('accountant ')

Insert a record. The following statement is used to obtain the id value:

Select @ identity as 'identified'

In my table infoclass, there is an ID field. Its original value is 14. After a new record is inserted, a value of 15 is automatically generated. for some reasons, the returned value is 15 after the preceding command is executed.

The following describes how to get the id value after the inserted record in. net.

Because sqlserver provides us with the Multi-query function, which greatly facilitates our work. See:

Dim SQL as string = "insert into jobs (job_desc, min_lvl, max_lvl) values ('a new job', 25,100 );"&_

"Select job_id from jobs where job_id = @ identity"

Dim cmd as new sqlcommand (SQL, CN)

As shown above, sqlcommand can execute multiple statements, separated. The first insert statement is executed, and the second statement returns the id value of the last inserted record. Because the query returns a single column and the value is unique, you can use executescalar to obtain the value:

Dim jobid as integer = CINT (CMD. executescalar ()

2. After insert into, get the ID for automatic insertion (select @ identity)

 

After the insert statement is run, run select @ identity to obtain the automatically generated ID.

Select scope_identity () as ID is recommended for SQL Server.

Because @ identity

Similar ident_current ('table ')

Ident_current returns the last generated id value for any session and specific tables in any scope. Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the value generated for a specific table in any session and scope.

@ Identity returns the last generated id value for any table in all scopes of the current session.

Scope_identity returns the last generated id value for the current session and any table in the current scope.

Scope_identity and @ identity return the last id value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.