把所有表合并成一个表(SQL 合并问题)
表a
id   tag
1     aa
2     bb

表b
id   tag
1     cc
2     dd

表c
id   tag
1     dd
2     ff

我想把所有数据合并到表D,并且是每次从每个表读一条数据
表d
id   tag   table
1     aa     a
2     cc     b
3     dd     c
4     bb     a
5     dd     b
6     ff     c

怎么写?

------------------------------------------------

如果是下面这样写就是一个一个表读了
INSERT   INTO   d
            (tag,type)
SELECT   tag,   'a '   AS   type
FROM   a
INSERT   INTO   d
            (tag,type)
SELECT   tag,   'b '   AS   type
FROM   b
INSERT   INTO   d
            (tag,type)
SELECT   tag,   'c '   AS   type
FROM   c

------------------------------------------------

这样写也是没按顺序一个表一个表读

INSERT   INTO   d
            (tag,type)
(
SELECT   tag,   'a '   AS   type
FROM   a
UNION
SELECT   tag,   'b '   AS   type
FROM   b
UNION
SELECT   tag,   'c '   AS   type
FROM   c
)

------解决方案--------------------
INSERT INTO d
(tag,type)
select tag,type
from (
SELECT tag, 'a ' AS type,id*100+1 as ordernum
FROM a
UNION all
SELECT tag, 'b ' AS type,id*100+2 as ordernum
FROM b
UNION all
SELECT tag, 'c ' AS type,id*100+3 as ordernum
FROM c
) as t
order by ordernum

------解决方案--------------------
create table A(id int, tag varchar(10))
insert A select 1, 'aa '
union all select 2, 'bb '

create table B(id int, tag varchar(10))
insert B select 1, 'cc '
union all select 2, 'dd '

create table C(id int, tag varchar(10))
insert C select 1, 'dd '
union all select 2, 'ff '

select id=identity(int, 1, 1), tmp.tag, tmp.[table] into D
from
(
select top 100 percent tmp.tag, tmp.[table] from
(
select id, tag, [table]= 'A ' from A
union all
select id, tag, [table]= 'B ' from B
union all
select id, tag, [table]= 'C ' from C
)tmp order by id, tag
)tmp

select * from D order by id

--result
id tag table
----------- ---------- -----
1 aa A
2 cc B
3 dd C
4 bb A
5 dd B
6 ff C

(6 row(s) affected)