sql-server - SQL 在主表中插入数据，然后在明细表中引用数据

Question

我有两个表:

• MasterReg(MasterID、收入、日期、描述)； MasterID 是一个Identity 列

• DetailReg (DetailID, MasterID, NumValue); DetailID 是一个Identity 列

我正在尝试使用 openxml 从 xml 字符串中插入数据

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

这将在我的 MasterReg 表中插入 5 行

DetailReg 表为 MasterReg 表中插入的每个值包含 6 行

例如对于 MasterID=1，DetailReg 表中将有六个 DetailID(DetailReg 中的六个条目对应一个 掌握入口)

我的 XML 看起来像这样:

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

我不想为此使用游标...第一个 MasterReg 条目已成功完成，但我不知道如何将相关数据插入 DetailReg 主注册表...

两个表最初都没有任何数据。

Answer 1

我不知道是谁投了反对票，但你的问题有两个优点:(1) 处理 XML 数据结构和 (2) 处理主子插入而不求助于游标。

这是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

如果您需要帮助理解查询，请在评论中告诉我。