SQL Inserting data in master table and then reference data in detail table

Question

I have two tables:

• MasterReg (MasterID, Revenue, Date, Desc); MasterID is an Identity column

• DetailReg (DetailID, MasterID, NumValue); DetailID is an Identity column

I am trying to insert data from a xml string using openxml

insert into MasterReg (Revenue, Date, Desc) 
   Select Revenue, Date, Desc 
  From OPENXML(....

this will insert 5 rows in my MasterReg table

DetailReg table contains 6 rows for each of value inserted in MasterReg table

e.g. for MasterID=1 there will be six DetailID in DetailReg table (six entries in DetailReg for one Master entry)

My XML looks like this:

<Root>
   <Detail>
      <Revenue>333300</Revenue>
      <Date>21/6/2011</Date>
      <Desc>desc text...</Desc>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
   </Detail>
   <Detail>
      <Revenue>333300</Revenue>
      <Date>21/6/2011</Date>
      <Desc>desc text...</Desc>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
   </Detail>
   <Detail>
      <Revenue>333300</Revenue>
      <Date>21/6/2011</Date>
      <Desc>desc text...</Desc>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
      <NumValue value="56"/>
   </Detail>
</Root>

I don't want to use cursor for this one...The first MasterReg entry done successfully but I don't have any idea about how to insert associated data into DetailReg with master reg table...

Both tables initially do not have any data in them.

Answer 1

I don't know who voted you down but your question has two merits: (1) dealing with XML data structure and (2) handling master-child inserts without resorting to cursor.

This is a perfect job for MERGE:

SET DATEFORMAT DMY -- Your server may not need this
DECLARE @XMLString xml = '<!-- your xml goes here --!>'
DECLARE @InsertResult TABLE
(
    MasterID    int,
    NumValueXML xml
)

MERGE MasterReg
    USING   (
                SELECT  Detail.value('Revenue[1]','int')        AS Revenue,
                        Detail.value('Date[1]','date')          AS [Date],
                        Detail.value('Desc[1]','varchar(200)')  AS [Desc],
                        Detail.query('NumValue')                AS NumValueXML
                FROM    @XMLString.nodes('/Root/Detail') tmp(Detail)
            ) AS src
    ON      0 = 1
    WHEN NOT MATCHED THEN
            INSERT (Revenue, [Date], [Desc])
            VALUES (src.Revenue, src.[Date], src.[Desc])
    OUTPUT  inserted.MasterID, src.NumValueXML
    INTO    @InsertResult (MasterID, NumValueXML)
;

INSERT INTO DetailReg (MasterID, NumValue)
    SELECT      t1.MasterID,
                t2.x.value('@value','int')
    FROM        @InsertResult                    t1
    CROSS APPLY t1.NumValueXML.nodes('NumValue') t2(x)

SELECT * FROM MasterReg
SELECT * FROM DetailReg

Let me know in the comment if you need help understand the query.