Say you want to combine the data from 3 tables - a1, a2, and a3.
Now, you also want to add a special field, let's say it's the year, and it's 2001 for a1 2002 for a2 etc'
In addition, you want the resluting table to have an identity column. What do you do?
This is a real - life scenario which came up today, and here's the solution:
select IDENTITY(int, 1,1) AS id_num , *
into a
from
(
select 2001 as year ,* from a1
union
select 2002,* from a2
union
select 2003,* from a3
) as t
select * from a