June 1, 2015 at 3:19 pm
Hello, I have multiple temp tables that return one row of results each. What would be the best way to combine these results into one giant temp table. I tried using UNION and UNION ALL after each drop table but that didn't work. If anyone has a suggestions, please let me know. Thanks!
June 1, 2015 at 7:50 pm
Union all should have worked but it's impossible to tell without seeing your code, at the very least.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2015 at 9:26 am
Thanks! I feel a union all would work too but it just doesn't. I have temp tables and I think that is what is messing it up. Here is what I have so far:
Select columns
into #tableA
from
where
order by
with
as (select
from #tableA
group by)
select
from
group by
drop table #tableA
union all
Select columns
into #tableB
from
where
order by
with
as (select
from #tableB
group by)
select
from
group by
drop table #tableB
I get an error that says "Incorrect syntax near the keyword 'union'." I'm not sure of any other way to structure this.
June 3, 2015 at 9:41 am
You have your syntax wrong. Additionally, CTEs won't work on SQL Server 2000 and previous.
For 2005+ this syntax would work.
Select columns
into #tableA
from SometableA
where SomeCondition
--order by --unnecessary
;
Select columns
into #tableB
from SomeTableB
where SomeCondition
--order by --unnecessary
;
WITH
CTE1 AS (
select columns, aggregation
from #tableA
group by columns
),
CTE2 AS (
select columns, aggregation
from #tableB
group by columns)
SELECT somecolumns, aggregation
FROM CTE1
GROUP BY somecolumns
UNION ALL
SELECT somecolumns, aggregation
FROM CTE2
GROUP BY somecolumns;
DROP TABLE #tableA;
DROP TABLE #tableB;
It seems that you're creating many unnecessary steps. This should give the same result with less work:
SELECT columns, aggregation
FROM SometableA
WHERE SomeCondition
GROUP BY columns
UNION ALL
SELECT columns, aggregation
FROM SomeTableB
WHERE SomeCondition
GROUP BY columns
ORDER BY somecolumn;--if needed
All the code posted here is pseudocode and won't work as it is.
June 3, 2015 at 10:11 am
Awesome, that worked! Thank you so much!
June 3, 2015 at 10:18 am
Hopefully, you did understand why were you getting an error and how the syntax works. CTEs and UNION (ALL) are part of a single statement and can't be divided by, for example, a drop table statement.
June 3, 2015 at 10:23 am
Yes, got it, thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply