时间戳

首页 > 解决方案 > 在 SQL Server 中插入递归 CTE

sql - 在 SQL Server 中插入递归 CTE

问题描述

我有一个跨两个字段具有父子记录关系的表。我编写了一个带有递归 cte 的查询，它将所有子记录返回到特定的记录。现在我需要将它们插入到另一个表中，在该表中使用 Recorded、ParentId 字段设置父子关系。RecordId 是标识符，主键。有什么方法可以在 CTE 中进行插入，以便在获取（和插入）子记录作为 ParentId 时可以使用标识符（RecordId）？

源表架构：

CREATE TABLE dbo.table1 
(
    PartIndex1 nvarchar(30) NOT NULL,
    PartName1 nvarchar(30) NOT NULL,
    PartType nvarchar(5) NOT NULL,
    PartIndex2 nvarchar(30) NOT NULL,
    PartName2 nvarchar(30) NOT NULL,
    Qty int NOT NULL,

    CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED (PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty)
) ON [PRIMARY]
GO

示例数据脚本：

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN001', N'B', N'PI1', N'PN002', 1)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN001', N'D', N'PI1', N'PN003', 1)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN002', N'D', N' ', N'B01', 40)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN002', N'D', N'PI1', N'PN003', 2)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES(N'PI1', N'PN002', N'D', N'PI2', N'PN004', 1)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN006', N'B', N'PI1', N'PN002', 3)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN006', N'B', N'PI2', N'PN004', 1)
GO

INSERT INTO dbo.table1(PartIndex1, PartName1, PartType, PartIndex2, PartName2, Qty) 
VALUES (N'PI1', N'PN007', N'B', N'PI1', N'PN003', 2)
GO

我的递归 CTE 查询和结果：

; WITH cte AS
(SELECT
    *
  FROM dbo.table1
  WHERE partIndex1 = 'PI1'
  AND partName1 = 'PN001'

  UNION ALL

  SELECT
    t2.*
  FROM cte o
  JOIN dbo.table1 t2
    ON o.partIndex2 = t2.partIndex1
    AND o.partName2 = t2.partName1)
SELECT
  *
FROM cte


PartIndex1   PartName1  PartType PartIndex2  PartName2  Qty
------------ ---------- -------- ----------- ---------- ---
PI1          PN001      B        PI1         PN002      1
PI1          PN001      D        PI1         PN003      1
PI1          PN002      D                    B01        40
PI1          PN002      D        PI1         PN003      2
PI1          PN002      D        PI2         PN004      1

我有“PN001”的所有子记录。我需要将它插入到其他表中。

目标表：

CREATE TABLE dbo.table2 
(
    RecordId INT IDENTITY,
    Parent INT NULL,
    SetName NVARCHAR(128) NOT NULL,
    PartIndex1 NVARCHAR(30) NOT NULL,
    PartName1 NVARCHAR(30) NOT NULL,
    PartType NVARCHAR(5) NOT NULL,
    PartIndex2 NVARCHAR(30) NOT NULL,
    PartName2 NVARCHAR(30) NOT NULL,
    Qty INT NOT NULL,

    CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED (RecordId)
)
ON [PRIMARY]
GO

预期结果：

RecordId  ParentId SetName   PartIndex1   PartName1  PartType PartIndex2  PartName2  Qty
--------- -------- --------- ------------ ---------- -------- ----------- ---------- ---
42351     NULL     DataSet41 PI1          PN001      B        PI1         PN002      1
42352     NULL     DataSet41 PI1          PN001      D        PI1         PN003      1
42353     42353    DataSet41 PI1          PN002      D                    B01        40
42354     42353    DataSet41 PI1          PN002      D        PI1         PN003      2
42355     42353    DataSet41 PI1          PN002      D        PI2         PN004      1

标签： sqlsql-serverinsertcommon-table-expressionrecursive-cte

解决方案

假设，您已经在数据库中创建了表“dbo.table2”。

您可以使用此查询：- 但是，我不知道您如何获取 ParentId 的值。

    insert into dbo.table3 
    ( Parent,
      SetName,
      PartIndex1,
      PartName1,
      PartType,
      PartIndex2,
      PartName2,
      Qty)
select t2.Parent,'DataSet41' as SetName,t1.PartIndex1,t1.PartName1,t1.PartType,t1.PartIndex2,
t1.PartName2,t1.Qty from dbo.table1 t1 left outer join dbo.table3 t2 on t1.partindex1=t2.partindex1

请更新您如何获取 productid 的值，因为它既不是标识列，也没有在 dbo.table1 中使用。

推荐阅读