insert into b
select field1,firld2,...
from A1
union all
select field1,firld2,...
from A2
union all
.
.
.
union all
select field1,firld2,...
from A50
select field1,firld2,...
from A1
union all
select field1,firld2,...
from A2
union all
.
.
.
union all
select field1,firld2,...
from A50
set @i = 1
while @i <=50
begin
exec(' insert into b select * from A'+ltrim(@i))
set @i = @I +1
end
declare @sql varchar(max)
declare @i tinyint
set @sql = 'insert into b '
set @i = 1
while @i < 50
begin
set @sql = @sql + 'select * from a' + convert(varchar(2),@i) + ' union all '
set @i = @i + 1
end
set @sql = @sql + 'select * from ' + convert(varchar(2),@i)
print @sql
我的数据库里有50个数据表(A1、A2、A3、A4、A5、A6等),现在我想把这些表里的数据内容汇总到表B里,数据表的结构是相同的。id自动添加。
*/
insert into b
select * from A1
union all
select * from A2
union all
select * from A3
union all
select * from A4
union all
select * from A5
union all
select * from A6
...
declare @sql varchar(max)
select @sql =
stuff((select '; insert into b select * from '+ name from sysobjects where xtype = 'U'
and name like 'A%'
for xml path('')),1,1,'')
exec(@sql)