An SQL statement is used to obtain the auto-increment column.

Today, I made an application to insert the tab_1 table, and I need to get the auto-increment id value and insert it into tab_2, vaguely remember that there is a global variable @ identity in the SQL statement to implement such business needs. I wrote the following SQL statement.
String SQL = "insert into tab_1 (col_1) values ('value _ 1') \ r \ n insert into tab_2 (col_1) values (select @ identity )"
The result is an error.

Google searched for the keyword "SQL get auto-incrementing column insertion" and found N multiple answers, which can be divided into several categories:
1. after tab_1 is inserted, use sqlheper. executescalar executes an SQL statement such as "select @ identity" to return an object. In this way, the auto-incrementing column needs to be retrieved from the program and then concatenated with the SQL statement.

2. You can also see a male written like this:
String SQL = "insert into tab_1 (col_1) values ('value _ 1') \ r \ n insert into tab_2 (col_1) values (select cast (@ scope_identity () as INT) "The result returns an error.

Think: At this time, I stood up and poured tea. Think about it. Isn't it possible to think of an SQL statement? When I suddenly thought about the source code of petshop, there was a similar implementation.
String SQL = "insert into tab_1 (col_1) values ('value _ 1') \ r \ n declare @ uid int \ r \ n select @ uid = scope_identity () \ r \ n insert into tab_2 (col_1) values (@ UID )"
At this time, we were excited to refresh F5, and there was no error when the operation was successful. We checked that the auto-incrementing primary key generated when the database was inserted to TAB_1 had already been inserted into TAB_2, while implementing the business logic, it also reaches my expectation, Perfect... with excitement, I wrote this log and recorded my mood. The last step is to attach a piece of information collected online.
Certificate --------------------------------------------------------------------------------------------------------------------------------------------
@ IDENTITY
Returns the last inserted id value.

Syntax
@ IDENTITY

Return type
Numeric

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 jobs (job_desc, min_lvl, max_lvl)
VALUES ('accountant', 12,125)
SELECT @ identity as 'identified'
---------------------------------
The help document of SQL is copied directly. In fact, Microsoft's help document is the best teacher. It is best to learn to directly view help documents.