Create microsoft sql table now that i'm far into my query

The Sandbox Software/Technology
1

So, i have a query and have several strings of CTEs as part of my query and at some point things slow down, a great deal. One of my last CTES we’ll call bigtable and following are minitable1, minitable2…which are all subsets of bigtable where criteria1 and criteria2 are met.

If i do a select * from bigtable, the query runs in a reasonable amount of time.

If i do a select * from any of my minitables, the query takes an irrationally long amount of time.

To get around this, a coworker suggested i make a temp table of bigtable, which sounds good, but bigtable is the result of several CTEs.

Can making a temptable be as simple as

create table #importanttemptable as

select * from
(
with cte1 as (…)
, cte2 as (…)
…
, bigtable as (…)
select * from bigtable
)
?

What’s the easiest way to make a temp table out of a CTE, several CTEs in the making?

Till all are one, Epistemus

2

I did a little reading about why temp table might be a solution. It seems that CTEs are in memory, but temp tables are in tempdb, so maybe you are grinding on a memory limit.
You might separate the construction of the temp table and the insertion of the records into the table.
CREATE TABLE #LocalTemp
(
UserID int,
Name varchar(50),
Address varchar(150)
);
INSERT INTO #LocalTemp
WITH CTE1…
SELECT * FROM BIGTABLE

The tricky part is the ; that you need before the CTE. Not sure if the INSERT INTO will make ugly things happen.

Oh, and a trick I was told to help handle large INSERTs is to use WITH (TABLOCK) after the table name. Not sure if that is needed in a temp table.

Some professional commentary