the best method for insert bulk data into master/detail tables with stored-procedure?

Question

Suppose, I have a master table the name of Order and detail table called the OrderDetail which the OrderId is identity key and using in OrderDetail as foreign key. Now i want insert bulk data with a stored procedure into Order and then insert the relevant details into OrderDetail table. Can anyone tell me the best way to do it? How do get identity value from master and the detail table i use?

check out the answe here: (http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) — Rigerta, Apr 24 '17 at 07:41
@rigerta Note! I have bulk insert into master table simultaneously. — Aiyoub A., Apr 24 '17 at 07:47
Are you asking how to insert multiple orders into an `Order` table that has an `identity` column `OrderId` and then correlate the newly assigned `OrderId` values with `OrderDetail` rows? Table schemas and sample data would make it much clearer. Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Apr 24 '17 at 13:03

score 2 · Accepted Answer · edited Jun 07 '17 at 07:00

You could use the OUTPUT clause as follows:

BULK INSERT into Orders table and store all Id's from the BULK INSERT into a table variable. After that, insert details into OrderDetail getting the OrderId from the table variable where you have them stored already.

You can check a working demo of this code here.

     DECLARE @outputtbl TABLE ( id INT );
     --here you will store the bulk inserted id's 
     --here you will do the bulk insert (note that i used a union all of several      
         selects as a "source" for your bulk insert)
     INSERT INTO Orders
     OUTPUT inserted.id
            INTO @outputtbl ( id )
     SELECT *
     FROM   ( SELECT    1 AS id ,
                        GETDATE() AS dt
              UNION ALL
              SELECT    2 AS id ,
                        GETDATE() AS dt
              UNION ALL
              SELECT    3 AS id ,
                        GETDATE() AS dt
              UNION ALL
              SELECT    4 AS id ,
                        GETDATE() AS dt
            ) t;

     --inserting into OrderDetail, OrderId's from the table variable and other fields as per your logic. (`item name` here it's just an example)
     INSERT INTO OrderDetail
            ( orderid, itemname )
     SELECT id ,
            'itemx'
     FROM   @outputtbl;

I created two simple tables Orders and OrderDetail to simulate the problem.

Aside: You may want to read [this](http://stackoverflow.com/a/12078717/92546) answer about using repeated called to `GetDate()` in a query. — HABO, Apr 24 '17 at 13:00
Thanks! :) In the answer provided to the current question, the getdate() value had absolutely no importance as it could have been any constant value or even not been there at all. It was used for inserting into an "example" column. Very good link though! — Rigerta, Apr 24 '17 at 13:05

score 1 · Answer 2 · edited Jun 07 '17 at 05:05

This is just a sample table and i am inserting bulk of sample data in my table

DECLARE @Counter INT 
SET @Counter = 1
    WHILE @Counter < 50000
        BEGIN 
         INSERT [SampleTableName] VALUES(Id)
            SELECT 
                NEWID() -- i have a column sample_id so i am entrying random 
                newid() into that
                ABS (CHECKSUM(NEWID())) % 60 + 1,
                DATEADD ( DAY, ABS(CHECKSUM(NEWID()) % 3650), '2007-04-01')
                -- i have a sample date field as well and i am entrying 10 
                   years of date in that
            SET @Counter += 1
        END

the best method for insert bulk data into master/detail tables with stored-procedure?

2 Answers2