vendredi 24 juin 2016

Prevent CTE from disappearing after select statement

When I use a recursive common table expression and then use that cte to insert several records, how can I do this twice? Essentially, I am doing the following: with cte_parents as (select customerid, parentCustomerId from customer where customerid = @0 union all select c.customerid, cte_parents.customerid from customer c join cte_parents on cte_parents.customerid = c.ParentCustomerId ) insert into customercontact select customerid, @1 from cte_parents insert into customercontacthistory select customerid, @1, GetDate() from cte_parents this fails and says Invalid object name 'cte_parents'. If i do it like this: with cte_parents as (select customerid, parentCustomerId from customer where customerid = @0 union all select c.customerid, cte_parents.customerid from customer c join cte_parents on cte_parents.customerid = c.ParentCustomerId ) insert into customercontact select customerid, @1 from cte_parents; with cte_parents_history as (select customerid, parentCustomerId from customer where customerid = @0 union all select c.customerid, cte_parents_history.customerid from customer c join cte_parents_history on cte_parents_history.customerid = c.ParentCustomerId ) insert into customercontacthistory select customerid, @1, GetDate() from cte_parents_history it succeeds. the two Common Table Expression statements are identical, with the exception of naming them differently during the with clause.

Aucun commentaire:

Enregistrer un commentaire