Querying against a Union Query In SQL Server 2005

[snodrift1]

I've been using Access for several years and am now switching to SQL Server 2005. Once I have created a union query How can I query against my union? I would assume that I need to name my Union Query but that does not seem to be working for me as I have done to the previous queries that makeup the Union Query. This is my union query.

Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP01
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP02
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP03
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP04
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP05
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP06
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP07;

 

[SQL_Hell]

Hi,

Unions are typically bad for performance, I tend to avoid them as much as possible. I suppose if this is a report then it's not soo bad.
It would be better to build up a temporary table imo, but it depends on the row count really.

To query the union script create it as a view, then query that view

 

[snodrift1]

I agree with you on the table option but I don't have that luxury. How do I make it a view?

 

[SQL_Hell]

Run this in sql server management studio query (I bunched up your code a bit, otherwise this would have been a long post)

CREATE VIEW [vw_UnionView] (
AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
) AS
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP01
union all
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP02
union all
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP03
union all
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP04
union all
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP05
union all
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP06
union all
Select AssyNo,PrimaryNo,PrimaryQty,PrimaryScrp,ComponentNo,CompQty,STNDCOST,ScrapPer,LOCNCODE,Levels,Sort
From CSTRLUP07
GO

Then to query

select * from  vw_UnionView

How come you are not able to change the code to use a temp table?

 

[pilsdumps]

No need to create views or tables - you can use the union query as a sub query and run the query on that, or even better use the with clause:

;with mytable
(
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP01
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP02
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP03
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP04
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP05
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP06
union all
Select AssyNo
,PrimaryNo
,PrimaryQty
,PrimaryScrp
,ComponentNo
,CompQty
,STNDCOST
,ScrapPer
,LOCNCODE
,Levels
,Sort
From CSTRLUP07
)
select * from mytable