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.
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire