SELECT @ @IDENTITY

Use SELECT @ @identity to get the ID that was automatically generated when the record was last inserted

If you use a stored procedure, it will be very simple, the code is as follows: SET @[email protected] @IDENTITY

Description

After an INSERT, SELECT into, or bulk copy statement is complete, the @ @IDENTITY contains the last identity value produced by this statement. If this statement does not affect any table with an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted, multiple identity values are generated, and the @ @IDENTITY returns the identity value that was last generated. If this statement fires one or more triggers that perform an insert operation that produces an identity value, the call @ @IDENTITY immediately after the statement executes returns the last identity value produced by the trigger. If the INSERT or SELECT into statement fails or the bulk copy fails, or the transaction is rolled back, the @ @IDENTITY value is not reverted to the previous setting.

The @ @IDENTITY, scope_identity, and ident_current functions are similar when you return to the last value of the @ @IDENTITY column that is inserted into the table.

@ @IDENTITY and Scope_identity will return the last identity value that was 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 returns the identity value generated for a particular 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 identity column and displays the identity value used in the new row with @ @IDENTITY.

INSERT into Infoclass (infoclass) VALUES (' Accountant ')

Then we insert a record, below we use the statement to get the identity value:

SELECT @ @IDENTITY as ' IDENTITY '

My table Infoclass has an ID field, its original value is 14 after inserting a new record automatically generated a value of 15, because, the above command executes after the return value: 15.

Here's how to get the ID value after inserting a record in. Net.

Because SQL Server provides us with the ability to query multiple queries, this greatly facilitates our work. Please see:

Dim SQL as String = "INSERT into jobs (JOB_DESC,MIN_LVL,MAX_LVL) VALUES (' A new job ',", ");" & _

"Select job_id from jobs WHERE job_id = @ @IDENTITY"

Dim cmd as New SqlCommand (SQL, CN)

As you can see above, SqlCommand can execute multiple statements with ";" between the statements. Separated. The first executes the insert, and the second returns the ID value of the last inserted record, because the query returns a single column and the value is unique, so you can use ExecuteScalar to obtain:

Dim jobId as Integer = CInt (cmd. ExecuteScalar ()) A

2. Insert into to get an auto-Insert ID (SELECT @ @identity)

Example:

Insert a new statement in the Class table and select @ @IDENTITY; The results returned after execution are as follows:

Insert into Class (Classcode,classname) VALUES (111, ' xiaoming '); select @ @IDENTITY;

When you finish running the INSERT statement, execute the SELECT @ @identity to get an auto-generated ID

If SQL Server is best with select Scope_identity () as ID

Because @ @identity a global

The same kind of ident_current (' table ')

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 identity value that was last 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.

Some of the content comes from links: http://blog.163.com/zhangqian_sms/blog/static/544483382008925112539620/

SELECT @ @IDENTITY