Insert data to Master and Detail table at the single script with multi-data using T-SQL

Question

I've to insert data to Master and Detail records, however what can I do if I need to insert both data at the single script with T-SQL.
Question
1.How can I get the Master Index Key(Identity increment) when data's was inserted
2.and then get Index Key to Detail record at the same time(in single script)

For example(hopefully result):

Master table

Detail table

the column "SeatMasterIndexID" data is from Master "IndexID"

PS: pseudocode

INSERT INTO MasterTable (TeamId ,  BranchId , SeatId) OUTPUT Inserted.IndexID ......
INSERT INTO DetailTable (TeamId ,MasterIndexID) values('1',Inserted.IndexID <-- from output Inserted.IndexID)

Possible duplicate of [Fastest way to perform nested bulk inserts with scope\_identity() usage?](https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage) — Zohar Peled, Aug 09 '18 at 04:59

AB_87 · Accepted Answer · 2018-08-09T04:06:57.570

You can use SCOPE_IDENTITY if you are inserting one row at a time.

If you are inserting multiple rows (like from a select statement) into your Master table. You can you OUTPUT CLAUSE to get all your inserted Ids from Master table into a temp table (you will have to create this temp table in advance).

Since you have not provided source query to populate Master and detail tables, I have provided basic query which inserts into both tables.

IF OBJECT_ID('tempdb..#MasterTable') IS NOT NULL
    DROP TABLE #MasterTable;

IF OBJECT_ID('tempdb..#DetailsTable') IS NOT NULL
    DROP TABLE #DetailsTable;

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;

CREATE TABLE #MasterTable
    (
        IndexId INT IDENTITY(1, 1) ,
        TeamId INT ,
        BranchId INT ,
        SeatId INT
    );



CREATE TABLE #DetailsTable
    (
        IndexId INT IDENTITY(1, 1) ,
        TeamId INT ,
        BranchId INT ,
        SeatId INT ,
        SeatMasterIndexId INT
    );


CREATE TABLE #TempTable
    (
        IndexId INT ,
        TeamId INT ,
        BranchId INT ,
        SeatId INT
    );

INSERT INTO #MasterTable ( TeamId ,
                           BranchId ,
                           SeatId )
OUTPUT Inserted.*
INTO #TempTable
VALUES ( 1 , -- TeamId - int
         2 , -- BranchId - int
         2   -- SeatId - int
    );

INSERT INTO #DetailsTable ( TeamId ,
                            BranchId ,
                            SeatId ,
                            SeatMasterIndexId )
            SELECT TeamId ,
                   BranchId ,
                   SeatId ,
                   IndexId
            FROM   #TempTable;

SELECT *
FROM   #MasterTable;
SELECT *
FROM   #DetailsTable;

thank very much, however your example hasn't dealt with my problem,I've multiple rows that have to insert to both table at the single script,please give me more detail or example if you can help me thanks — Willie Cheng, Aug 09 '18 at 03:41
you will have to provide more detail in your question. May be you have select statement which inserts into Master table? Then you must be having another data source to insert into Detail table — AB_87, Aug 09 '18 at 03:44
You have not provided any detail about your source data. I have updated answer based on simple insert. — AB_87, Aug 09 '18 at 04:05
@WillieCheng if you want to add multiple records to the master table and their corresponding records to the details table you should have specified it clearly in your question. You can do it using the technique I've shown in [this SO post.](https://stackoverflow.com/questions/38213008/t-sql-insert-data-into-parent-and-child-tables?noredirect=1&lq=1). A simpler version can be found on [this SO post.](https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231) — Zohar Peled, Aug 09 '18 at 04:58

Willie Cheng · Answer 2 · 2018-08-10T02:33:56.167

0

I've found another idea

INSERT INTO MasterTable ( TeamId ,  BranchId ,   SeatId )
OUTPUT INSERTED.IndexID, 1,1,1
INTO DetailsTable
(
IndexID,
TeamID,
BranchId ,   
SeatId
)
VALUES ( 1,1,1);

edited Aug 10 '18 at 02:33

answered Aug 09 '18 at 10:00

Willie Cheng

7,679
13
55
68

Insert data to Master and Detail table at the single script with multi-data using T-SQL

2 Answers2