Insert inside recursive CTE in SQL Server

Question

I have a table that has a parent-child record relationship across two fields. I wrote a query with a recursive cte that returns all child records to a particular one. Now I need to insert them to another table where the parent-child relation is set using the Recorded, ParentId fields. RecordId is identifier, primary key. Is there any way to do an insert inside the CTE so that a identifier (RecordId) appears that I can use when getting (and inserting) child records as ParentId?

Source table schema:

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

Sample data script:

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

My recursive CTE query and result:

; 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

I have all child records of 'PN001'. And I need to insert it into other table.

Target table:

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

Expected result:

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

Sumit Tiwary Sumit Tiwary · Accepted Answer · 2020-02-25T10:06:27

Assuming,You have already created table "dbo.table2" in your database.

You can use this query:- But,I don't know how you are getting values for 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

Please update how you are getting value for productid because its neither an identity column nor used in dbo.table1.

Insert inside recursive CTE in SQL Server

1 Answers