How to get an ID automatically generated after the last time you insert a record

Insert

How to get an ID automatically generated after the last time you insert a record

Author: Amoy Net

Source: Amoy Net

NOTE: Reprint please indicate the source

If you use stored procedures, it will be very simple, the code is as follows: SET @NewID =@ @IDENTITY

Description
After an INSERT, SELECT into, or bulk copy statement completes, the @ @IDENTITY contains the last identity value produced by this statement. If this statement does not affect any table that has an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted, multiple identity values are generated, and the @ @IDENTITY returns the last-generated identity value. If this statement fires one or more triggers that perform an insert operation that produces an identity value, calling the @ @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 restored to the previous setting.

The @ @IDENTITY, scope_identity, and ident_current functions are similar in terms of returning the last value inserted into the @ @IDENTITY column of the table.

@ @IDENTITY and Scope_identity will 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 are not limited by scope and session, but are limited by the specified table. Ident_current returns the identity values 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 the table with an identity column and displays the identity value used in the new row with the @ @IDENTITY.

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

When we insert a record, 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 produces a value of 15, because the above command after the execution of the return value: 15.

Let's talk about how to. NET, the ID value after the Insert record is obtained.

Because SQL Server provides us with the capabilities of 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 ())