有200多个表 tbl1, tbl2, tbl3 各个表结构不同,但都包含列 kID,现在想 把所有表的kID都查出来 放在一个结果里
如 select kID from tbl1, tbl2, tbl3 sql应该怎么写,上面这样肯定不行区分不开kID结果为下面形式kID1
2
3
4
如 select kID from tbl1, tbl2, tbl3 sql应该怎么写,上面这样肯定不行区分不开kID结果为下面形式kID1
2
3
4
调试欢乐多
select KID from tbl2 union all
select KID from tbl3 union all
...........
set @i=1
set @s=''
while @i<=200
begin
set @s=@s+'select KID from tb1'+cast(@i as varchar(10))+' union all '+char(10)
set @i=@i+1
end
exec(left(@s,len(@s)-len(' union all ')))
set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(kid int)
if object_id('tb1')is not null drop table tb1
go
create table tb1 (kid int)
insert tb1 select 1 if object_id('tb2')is not null drop table tb2
go
create table tb2 (kid int)
insert tb2 select 2 if object_id('tb3')is not null drop table tb3
go
create table tb3 (kid int)
insert tb3 select 3 --------------------------------------------------------
declare @n int,@m int,@sql varchar(400)
set @n=3 ------表个数
set @m=1while @m<=@n
begin
set @sql='insert tb select kid from tb'+ltrim(@m)
exec(@sql)
set @m=@m+1
end----------------------------------------------------------
select * from tb kid
-----------
1
2
3