September 28, 2009 at 11:47 am
I am new to using the CTE. I have built 3 using the BOL syntax of:
WITH TableName
AS
(select, from),
Another TableName
AS
(select, from, Join on TableName....),
AndAnotherTableName
AS
(select, from, Join on AnotherTableName....)
So...I now have 3 CTE's....and I want to drop each of their results into a temp table and then query against the temp table.....CAN I DO THAT??
Right after the creation of the CTE's, I create a temp table and try to select into it and I get an error at the CREATE statement....
Any pointers for me??? Thank you!!
September 28, 2009 at 11:50 am
CTEs only apply to the statement they appear in.
What you would need to do is load the first CTE into a temp table, then the second, then the third.
If you are using the content of the first in the second, you would use the temp table you just populated, instead of including the first CTE again.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 12:04 pm
September 28, 2009 at 12:27 pm
ok, the concept seems clear, yet I am having issues. First I created a #temp table
then tried building the CTE and inserting into the #temp Table. It says I anything preceeding the WITH expression for the CTE has to be followed by a ';'. I did that after the CREAT #Temp table...that worked....but then I did an INSERT INTO #Temp and it won't let me.
I tried creating selects into #Temp but that isn't working either...
Anymore advice for a CTE beginner?? I am needed to insert 3 sets of data into a temp table and be able to query that temp table. I wanted to do it with CTE's, so I could learn how!
Thank you!!
September 28, 2009 at 1:09 pm
There are (basically) two ways to do this:
;with CTE1 (Col1) as
(select 1 union all
select 2)
select Col1
into #T
from CTE1;
or
create table #T (
Col1 int);
;with CTE1 (Col1) as
(select 1 union all
select 2)
insert into #T (Col1)
select Col1
from CTE1;
Which one are you using?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 1:12 pm
What GSquared is alluding to with his sample code is that it would help if you would show us your code. It would be much easier to help you if we could see what you are doing.
September 29, 2009 at 8:57 am
September 29, 2009 at 9:26 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2009 at 11:28 am
I'm not sure why you think you need a CTE for this.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2009 at 11:59 am
September 29, 2009 at 12:05 pm
Proper forum etiquette would have you show your solution. There may be others with similar problems and your solution may be helpful.
September 29, 2009 at 12:23 pm
Lynn, in my opening question, I tried to show what I was doing. I cannot show table names/column names, due to the sensitivity of what I work on. I think I would get in trouble, so I am very careful as to what I divulge. Like I said, the CTE I was trying to create, I showed the "layout". Sorry for being so vague.....
September 29, 2009 at 12:36 pm
You can obfusicate your table and column names making it more generic such that you can show what you did to solve your problem.
People do it all the time when faced with such a situation.
September 29, 2009 at 3:31 pm
Angelindiego (9/29/2009)
I have a table that has hierarchical data in it. Small amounts...but I am trying to filter on a piece of data that has children. I was reading about the CTE...thought that might be a way to get those children in the temp table I can query off of. Trying something new....
Ah... got it. The examples were non-hierarchical in nature and I didn't understand why someone wouldn't do the table population directly. You're mostly correct in using a CTE to resolve the hierarchy. I say "mostly" because it depends on how often the hierarchy changes... if not so much, then the "Nested Set" model of hierarchical data would result in lightning quick lookups but is a bit more difficulty to maintain.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2009 at 4:12 pm
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply