USER_8675309 USER_8675309 - 1 year ago 54
SQL Question

How to use a CTE to insert multiple records based on hierarchy

Lets say I have a

table that contains
as a primary key and
as a foreign key and I want to cascade an insert statement to create a record for each of the Customers in the hierarchy chain.

I also have a CustomerContact table that has a clustered primary key of
, and

So if I have the following:

CustomerId: 1
ParentCustomerId: Null

CustomerId: 2
ParentCustomerId: 1

CustomerId: 3
ParentCustomerId: 2

And I pass
into my customer ID, but I want to create 3 (although in this case the 3 is a variable, the hierarchies could go deeper) so that I insert into a different table a row for each customer in the chain.

declare @1 as int --customerId
declare @2 as int --personId for the contact
declare @3 as datetime --DateCreated

set @1 = 1
set @2 = 1 --personId
set @3 = GetDate()

--I don't know how to use a CTE to get all the CustomerIds that are
--something like
--with cte_customers
--(select CustomerId from customer
-- where ParentCustomerId = @1

insert into CustomerContact
Values(@1, @2, @3)

How can I write a CTE to get the children of all the customers related to param
and create a record in
for each?

Answer Source

You need to use a recursive common table expression using union all. Here's a reduced example:

with cte as (
    select customerid, parentcustomerid
    from customer 
    where customerid = 1
    union all 
    select c.customerid, cte.customerid
    from customer c join cte on cte.customerid = c.parentcustomerid)
select * from cte; 

With your sample data, this will return 3 records and can also handle deeper relationships.