USER_8675309 USER_8675309 - 5 months ago 19
SQL Question

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

Lets say I have a

Customer
table that contains
CustomerId
as a primary key and
ParentCustomerId
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
CustomerId
,
PersonId
, and
DateCreated


So if I have the following:

Customer1:
CustomerId: 1
:
ParentCustomerId: Null


Customer2:
CustomerId: 2
:
ParentCustomerId: 1


Customer3:
CustomerId: 3
:
ParentCustomerId: 2


And I pass
1
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
--as
--(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
@1
and create a record in
CustomerContact
for each?

Answer

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.

Comments