T-SQL - Insert multiple records into Parent and Child Tables

Question

I would like to write a procedure to add multiple parent records with child records in a related table. This is the sort of thing you would do to add multiple Customers in an Customer table and Order items to an CustomerOrder table.

I know the process would be as follows:

1. Insert multiple Customers into Customer table using Insert - Select.
2. Get the primary key of the each Customer record (presuming it’s auto- numbered)
3. Loop: add multiple order records, with the foreign key set to the primary key in step 2 above.
4. (Possibly) update the parent invoice parent record with anything derived from the child records (such as a total).

I am inserting records into Customer table using Table type parameter and Customer table has auto increment ID, How can I pass this auto increment customer ids to Order table?

Answer 1

How can I pass this auto increment customer ids to Order table?

You can use OUTPUT clause to get the inserted id.

You can refer following sample.

CREATE TABLE Customer 
  ( 
     ID      INT IDENTITY, 
     NAME    VARCHAR(100), 
     ADDRESS VARCHAR(100), 
     PhoneNo VARCHAR(100) 
  ) 

DECLARE @tblInsertdIds TABLE 
  ( 
     ID INT 
  ) 

INSERT INTO Customer 
            (Name, 
             Address, 
             PhoneNo) 
OUTPUT      INSERTED.ID 
INTO @tblInsertdIds(ID) 
VALUES      ('C1', 
             'Address 1', 
             '123123123'), 
            ('c2', 
             'Address 2', 
             '34234324') 
--Get the newly inserted ids

SELECT * 
FROM   @tblInsertdIds 

Online Demo

You can check more details about OUTPUT clause at OUTPUT Clause (Transact-SQL)