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?

Answer 1

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.