首页 文章

T-SQL在一个语句中插入多个子记录

提问于
浏览 次
1

因此，您可以使用以下语法一次INSERT多行：

INSERT INTO Table
SELECT Field1,
       Expression2
  FROM DataSource

为行插入子记录的常见模式可能是以下语法：

INSERT INTO Table2 (Field1, Field2)
  VALUES (Expression1, Expression2)

UPDATE Table1
  SET Table2ID = SCOPE_IDENTITY()
  WHERE Table1ID = @ParentID

有没有办法合并这两个概念，快速插入一系列子记录，其中每个子记录的父ID是已知的（而不是全部相同），子记录的ID由 IDENTITY 列分配，以及父记录的位置是指儿童记录？

这是一个示例模式：

CREATE TABLE Table2 -- Child table
(
  Table2ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Field1 ...,
  Field2 ...,
)

CREATE TABLE Table1 -- Parent Table
(
  Table1ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  FieldA ...,
  FieldB ...,
  Table2ID INT NULL FOREIGN KEY REFERENCES Table2 (Table2ID)
)

（精明的读者可能会注意到这是一对一（零或一）关系 . 这表明只需使用 Table1 的密钥作为 Table2 的密钥 . 不幸的是，这在这里不起作用 . 这些表格是更大模式的一部分，其中 Table2 可以在多个其他表中包含父项，并且还可以包含独立的记录 . ）

我的输入是一个表变量 @IDs ，其中包含 Table1ID 的列表 . 此输入标识的 Table1 记录的子集具有 NULL Table2ID 值 . 我的目标是为每一个创建一个新的 Table2 记录，同时更新 Table1 记录以指向他们的新孩子 .

我已经尝试了CTE和 OUTPUT 子句的每一个组合，我能想到并找不到在每个新的 INSERTED.Table2ID 旁边插入 Table1ID 的方法 . OUTPUT 子句可以't reference columns that aren'是 INSERT 语句的目标表的一部分 .

不起作用：

WITH NewData (Table1ID, Field1, Field2)
AS
(
  SELECT Table1ID,
         Expression1,
         Expression2
    FROM Table1 INNER JOIN @IDs AS IDs
      ON Table1.Table1ID = IDs.Value
    WHERE Table2ID IS NULL
)
INSERT INTO Table2 (Field1, Field2)
  OUTPUT INSERTED.Table2ID, NewData.Table1ID -- Error here
  VALUES (NewData.Field1, NewData.Field2)

我能想到的唯一方法是：

  • 更新 Table2 的架构以包含 Table1ID . 这样， INSERT 语句可以从 SELECT 语句中获取 Table1ID ，根据没有子项的 Table1 父记录生成 Table2 字段，并将其放入实际插入的数据中 . 然后，后续语句可以反转引用的方向并填充 Table1 中的 Table2ID 值 .

  • 在 @IDs 输入序列上使用游标，并为每个新的 Table2 记录单独执行单行 INSERT 语句 . 然后相应的 Table1 记录可以立即 UPDATE 与 SCOPE_IDENTITY() .

但是，第一个选项需要永久更改模式以在此操作中存储临时数据，第二个选项需要手动迭代记录，否定准备将一批行插入单个语句中的性能优势数据库引擎 .

有没有更好的办法？

sql-server database tsql

1 回答

  • 0

    您的问题似乎有点奇怪 - 它在几个地方引用了具有“子”的父表，但是对于您描述的模式，Table1（父级）在任何时候都只能拥有一个Table2子级 - 这是一个一对一的关系 .

    实际上你已经注意到了这一点，在那里用父Table1 id插入多个Table2条目要容易得多（因为这样可行） .

    如果您想要的是一对一的关系，那么更常见的架构可能是：

    CREATE TABLE Table1 -- Parent Table
(
  Table1ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  FieldA ...,
  FieldB ...
)

CREATE TABLE Table2 -- Child table
(
  Table1ID INT NOT NULL PRIMARY KEY,
  Field1 ...,
  Field2 ...,
)

ALTER TABLE Table2 ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (Table1ID)
REFERENCES Table1 (Table1ID)
GO

    在这种情况下，不会生成子项中的主键，而是匹配父表中的PK .

    回复于

相关问题