How to obtain the ID automatically generated after the last insert record

 
How to obtain the ID automatically generated after the last insert record
Author: Tao Internet
Source: Taotao
Note: Please indicate the source for reprinting.
If you use a stored procedure, the code is as follows: SET @ NewID = @ 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 ())