title: How to use a CTE to insert multiple records based on hierarchy
body: <p>Lets say I have a <code>Customer</code> table that contains <code>CustomerId</code> as a primary key and <code>ParentCustomerId</code> 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.</p>
  
  <p>I also have a CustomerContact table that has a clustered primary key of <code>CustomerId</code>, <code>PersonId</code>, and <code>DateCreated</code></p>
  
  <p>So if I have the following:</p>
  
  <p>Customer1: <code>CustomerId: 1</code> : <code>ParentCustomerId: Null</code></p>
  
  <p>Customer2: <code>CustomerId: 2</code> : <code>ParentCustomerId: 1</code></p>
  
  <p>Customer3: <code>CustomerId: 3</code> : <code>ParentCustomerId: 2</code></p>
  
  <p>And I pass <code>1</code> 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.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>How can I write a CTE to get the children of all the customers related to param <code>@1</code> and create a record in <code>CustomerContact</code> for each?</p>