All geek questions in one place

SQL Insert into two tables Question

I use a stored procedure to insert data into a database, when it is inserted, it is automatically assigned an identifier, which is an identifier, as well as the main key of the table. I also need to insert data into a second table, of which this identifier is one of the columns that need to be inserted. But it has not been created yet, so I was wondering how you guys will solve this problem. I need this when they submit my form, which inserts the user, and then I need to add it to the table, which will basically document the user being created. Is it possible.? I am using Microsoft SQL Server 2005.

thanks

+4
source share
4 answers
INSERT INTO Table1 (SomeColumn) Values ('SomeValue') DECLARE @NewIdent int SELECT @NewIdent = SCOPE_IDENTITY() INSERT INTO Table2 (SomeColumn, T1ID) VALUES ('SomeValue',@NewIDent)

You can look at @@ IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT to find out which one best suits your needs. I suspect SCOPE_IDENTITY will do the job.

Here is a good article on what they all do.

+5
source

Use the output clause to get the identifier from the first insert and use this value in the second insert.

+2
source

Examine the result of the scope_identity () system function after the first insertion; it will contain the identifier of the previously inserted record (provided that the identifier is automatically generated using the identity column). As pointed out in the comments, this is better than using @@ identity, as @@ identification may be inaccurate or cause problems when triggers are present in the database or in high traffic situations where requests may be previously missed.

0
source

U can get a new identity inserted using SCOPE_IDENTITY ()

0
source

Source: https://habr.com/ru/post/1345741/

More articles:


All Articles