CODEWITHSUNDEEP
t-sqlsql-scripts
Willie Cheng
Willie Cheng

Reputation: 8263

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

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
enter image description here
Detail table
enter image description here

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)

Upvotes: 1

Views: 3089

Answers (2)

Willie Cheng
Willie Cheng

Reputation: 8263

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);

Upvotes: 0

AB_87
AB_87

Reputation: 1156

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;

Upvotes: 3

Related Questions