Union and into #sub tables - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union and into #sub tables

Author

Topic

Ashy Larry
Starting Member

2 Posts

Posted - 2009-02-13 : 16:37:09

I have to modify one of our existing reports.

The way this current report is set up there are three seperate SQL queries that have a union staement in between each of these queries. This joins all three tables together to give me 1746 rows.

The new modification forces me to create sub tables using the "into" statement. Then eventually left joining the two tables into one table. For each of the three queries I would be creating 2 tables.

When I try to run my query I get the error message of being allowed to only use the into statement prior to the union statement.

Is there any type of work around to get my tables.

I tried joining all the tables but it limits the size of the column to the size of the first table. Which is 295 rows. 3*295 = 885. About half the rows I need.

I using Crystal to display my report. My only thought it to create two other sub reports with the same format so they look like the same report.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 16:38:42

You should be able to do this with a derived table:

SELECT ...
INTO ...
(YourQueryGoesHere
) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 00:58:51

you may sound more clearer if you explain this with some data. as of currently given info, try as Tara suggested. if still it does work,please provide some sample data and explain what you want

Ashy Larry
Starting Member

2 Posts

Posted - 2009-02-17 : 10:17:41

Ok, here is a little more information.

Currently how my query is set up is as follows..

select stuff from table
union
select stuff from table
union
select stuff from table

But the new code I have put together relys on "select stuff from table" becoming...

select stuff
into #A
from table

select stuff
into #B
from table

Select * from #A, #B

Is there any way to union the above from three different tables?

An axample of this would be...

select stuff
into #A
from table

select stuff
into #B
from table

Select * from #A, #B

union

select stuff
into #C
from table

select stuff
into #D
from table

Select * from #C, #D

union

select stuff
into #E
from table

select stuff
into #F
from table

Select * from #E, #F

Can I union the select for each of the A and B, then C and D, and then E and F?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 10:28:17

you can do this

Select * from #A, #B

union

Select * from #C, #D

union

Select * from #E, #F

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources