Transact-SQL User's Guide

When you use union statements with other Transact-SQL commands:

The first query in the union statement may contain an into clause that creates a table to hold the final result set. For example, the following statement creates a table called results that contains the union of tables publishers, stores, and salesdetail:
```
use master
```
```
sp_dboption pubs2, "select into", true
```
```
use pubs2
```
```
checkpoint
```
```
select pub_id, pub_name, city into results 
from publishers 
union 
select stor_id, stor_name, city from stores 
union 
select stor_id, title_id, ord_num from salesdetail 
```
You can use the into clause only in the first query; if it appears anywhere else, you get an error message.
You can use order by and compute clauses only at the end of the union statement to define the order of the final results or to compute summary values. You cannot use them within the individual queries that make up the union statement.
You can use group by and having clauses within individual queries only; you cannot use them to affect the final result set.

You can also use the union operator within an insert statement. For example:

create table tour (city varchar(20), state char(2))

insert into tour 
    select city, state from stores 
    union 
    select city, state from authors

drop table tour

You cannot use the union operator within a create view statement.
You cannot use the union operator on text and image columns.
You cannot use the for browse clause in statements involving the union operator.


	Expand Search