CODEWITHSUNDEEP
sqlsql-serverselectinsert-into
SQLiz
SQLiz

Reputation: 143

SQL Server: INSERT INTO 2 tables

When I add a record into the PERSONS table, an ID column is auto incremented (identity).

I need this ID to insert a record into the ADDRESS table, so I will be able to join both of them.

Does anyone know how I can get this ID to use in the second statement when I have multiple rows to append?

Basically it would append one row and use the ID to append the row in the other table.

I would like to avoid to loop each record to do this.

Thank you in advance.

INSERT INTO PERSONS (FIRST_NAME, LAST_NAME)
    SELECT FIRST_NAME, LAST_NAME 
    FROM CONTACTS

INSERT INTO ADDRESS (PERSON_ID)
    SELECT ID 
    FROM PERSONS

Upvotes: 0

Views: 919

Answers (2)

user8601959
user8601959

Reputation:

To visualize answer that was given by @GordonLinoff and @scsimon I created a small demo. You can check full Microsoft guide for triggers here.

DDL:

CREATE TABLE CONTACTS ( FIRST_NAME varchar(50) , LAST_NAME varchar(50) ); GO CREATE TABLE PERSONS ( Id int IDENTITY(1,1) , FIRST_NAME varchar(50) , LAST_NAME varchar(50) ); GO CREATE TABLE ADDRESS ( PERSON_ID int ); GO CREATE TRIGGER TR_AI_Persons_PersonAddress ON PERSONS AFTER INSERT AS BEGIN INSERT INTO ADDRESS (PERSON_ID) SELECT Id FROM inserted END

DML: INSERT INTO CONTACTS(FIRST_NAME, LAST_NAME) VALUES('Bob', 'White'); INSERT INTO CONTACTS(FIRST_NAME, LAST_NAME) VALUES('Tom', 'Black'); INSERT INTO PERSONS (FIRST_NAME, LAST_NAME) SELECT FIRST_NAME, LAST_NAME FROM CONTACTS;
SELECT * FROM ADDRESS;

Upvotes: 0

Maurício Pontalti Neri
Maurício Pontalti Neri

Reputation: 1080

You can use an INSERT ... OUTPUT INSERTED ... INTO

INSERT @PERSONS
       (FIRST_NAME,
        LAST_NAME)
OUTPUT Inserted.Id
INTO @ADDRESS
SELECT *
FROM   @CONTACTS;

Or if your needs are more complex another possibility is a merge statement

DECLARE @CONTACTS table ( FIRST_NAME  varchar(50) , LAST_NAME varchar(50))
DECLARE @PERSONS table  (Id int identity , FIRST_NAME  varchar(50) , LAST_NAME varchar(50))
DECLARE @ADDRESS table  (PERSON_ID int)

INSERT @CONTACTS VALUES ('FIRST_NAME1' , 'LAST_NAME2'),  ('FIRST_NAME1' , 'LAST_NAME2');

MERGE @PERSONS t
using @CONTACTS s
on
t.FIRST_NAME = s.FIRST_NAME 
AND t.LAST_NAME = s.LAST_NAME
WHEN NOT MATCHED THEN INSERT (FIRST_NAME, LAST_NAME ) Values (s.FIRST_NAME , s.LAST_NAME)
OUTPUT Inserted.Id into @ADDRESS;

Table PERSONS

select * from @PERSONS
Id          FIRST_NAME                                         LAST_NAME
----------- -------------------------------------------------- --------------------------------------------------
1           FIRST_NAME1                                        LAST_NAME2
2           FIRST_NAME1                                        LAST_NAME2

Table ADDRESS

SELECT * from @ADDRESS
PERSON_ID
-----------
1
2

Upvotes: 1

Related Questions