Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-04-19 : 07:40:50
|
| Hi Team,I have a Common Table Expression. I have to use the result of CTE for Updating and Inserting into a table. The update is the first step; it works perfectly. When I use it for insert, it causes an error saying that Invalid object name 'MyCTE'. Can’t I reuse CTE?Note: When I reversed the order of insert and update, insert worked fine; but got error in updatePlease suggest a solutionThanks Lijo Cheeran Joseph |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-04-19 : 07:45:15
|
| CTE is valid only for one statement, so you need to either repeat the code twice or encapsulate the CTE inside view or table valued function. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-19 : 07:47:52
|
The CTE only exists within the query. In your first case (update and insert), INSERT statement is a separate query and the CTE does not exists any more in that query.You can make use of a temp table to store the result of the CTE and then insert / update using the temp table.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-19 : 10:55:31
|
| if you want it across after creation, why not use temporary table instead?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-04-19 : 11:44:18
|
| Thanks..Yes, I opted for a table variable since the amount of data is less.However, this begavior of CTE is disappointing. But one good thing is it forces us to use temp table which can reuse the result.ThanksLijo Cheeran Joseph |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-19 : 11:45:46
|
Good thing in CTE is recursion possibility.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
CTE reuse question