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



I have two table


MasterReg(MasterID,Revenue,Date,Desc) MasterID Identity field


DetailReg(DetailID,MasterID,NumValue) DetailID Identity field


I am trying to insert data from xmlstring 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 contain 6 row for each of value inserted in MasterReg table ex... for MasterID=1 there will be Six DetailID in DetailReg table(Six Entry in DetailReg for one DetailReg Entry)


My XMl String is



<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 dont want to use cursor for this one...The first MasterReg entry done Successfully but i doesn't have idea about how to insert in DetailReg associated with master reg table... Both table at initial have no data in it..


No comments:

Post a Comment