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)

Answer 1

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;