使用sp_xml_preparedocument在父表和子表中批量插入 [英] Bulk insert in parent and child table using sp_xml_preparedocument

问题描述

我正在使用sp_xml_preparedocument进行批量插入。但是我想在父表中进行批量插入，为每个新插入的行获取scope_identity，然后在子表中进行批量插入。

I am using sp_xml_preparedocument for bulk insertion. But I want to do bulk insert in parent table, get scope_identity for each newly inserted row and then bulk insert in child table.

我可以通过将表变量用于在过程中创建父表，然后在该表中插入我应该在父表中插入的数据。现在循环遍历游标中的每一行，插入实际表中，然后插入子表中。

I can do this by taking table variable for parent table in procedure and insert data in that table which I supposed to insert in parent table. Now loop through each row in cursor, insert in actual table and then in child table.

但是，有没有鼠标指针的连击方法？
我想要一些最佳解决方案

But is there any batter way without cursor? I want some optimum solution

推荐答案

如果您使用的是SQL Server 2008或更高版本，则可以按照这个问题。

If you are on SQL Server 2008 or later you can use merge as described in this question.

创建一个表变量，当对 Parent 。然后从表变量插入 Child 。

Create a table variable that will capture the generated id from the parent table along with the child XML when doing a merge against Parent. Then insert into Child from the table variable.

注意：我使用XML数据类型而不是openxml。

Note: I use the XML datatype instead of openxml.

表：

create table Parent
(
  ParentID int identity primary key,
  Name varchar(10) not null
);

create table Child
(
  ChildID int identity primary key,
  Name varchar(10) not null,
  ParentID int not null references Parent(ParentID)
);

XML：

<root>
  <parent>
    <name>parent 1</name>
    <child>
      <name>child 1</name>
    </child>
    <child>
      <name>child 2</name>
    </child>
  </parent>
  <parent>
    <name>parent 2</name>
    <child>
      <name>child 3</name>
    </child>
  </parent>
</root>

代码：

declare @Child table
(
  ParentID int primary key,
  Child xml
);

merge Parent as P
using (
      select T.X.value('(name/text())[1]', 'varchar(10)') as Name,
             T.X.query('child') as Child
      from @XML.nodes('/root/parent') as T(X)
      ) as X
on 0 = 1
when not matched then
  insert (Name) values (X.Name)
output inserted.ParentID, X.Child into @Child;

insert into Child(Name, ParentID)
select T.X.value('(name/text())[1]', 'varchar(max)'),
       C.ParentID
from @Child as C
  cross apply C.Child.nodes('/child') as T(X);

SQL小提琴

这篇关于使用sp_xml_preparedocument在父表和子表中批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！