Requirements: A stored procedure, we need to insert a piece of data in tables A and B, where the main foreign key relationship between table A and table B, the columns associated with table B are the primary keys of table A, we need to insert the primary key value of table A after the insertion is completed, we need to obtain the primary key value of table A, and then insert the data into table B.
After inserting a record, you want to get the return value of the primary key in its datasheet immediately. This primary key is automatically generated.
First, create a temporary table to test, the code is as follows:
@@IDENTITY
After an INSERT, SELECT IN, or bulk copy statement completes, the @@IDENTITY contains the last identification value produced by this statement. If this statement does not affect any tables with identified columns, @@IDENTITY returns NULL. If multiple rows are inserted, multiple identifier values are generated, @@IDENTITY the last identifier value is returned. If this statement triggers one or more triggers that perform an insert operation that produces an identified value, calling @@IDENTITY immediately after the statement is executed will return the last identification value generated by the trigger. If the INSERT or SELECT INTO statement fails or mass replication fails, or if the transaction is rolled back, the @@IDENTITY value is not restored to its previous settings.
OUTPUT INSERTED
For INSERT, you can reference the inserted table to query the properties of the new row
The test code is as follows:
The effect is as follows:
|
Posted on 7/7/2020 11:55:16 AM
|
|
|
|
Landlord