--假设数据库分别为DB1、DB2、DB3、DB4,表名分别为T1、T2、T3...--把各个库中的表合并到DB1中,如下: insert into DB1.dbo.T1 select * from DB2.dbo.T1 union all select * from DB3.dbo.T1 union all select * from DB4.dbo.T1 union all select * from DB5.dbo.T1 union all select * from DB6.dbo.T1 union all select * from DB7.dbo.T1 union all select * from DB7.dbo.T1 union all select * from DB8.dbo.T1 union all select * from DB9.dbo.T1 union all select * from DB10.dbo.T1--这个语句可以在这10个库中任意一个库中执行。
--如果表中有自动ID列、或计算公式列,需要指定字段名。insert into DB1.dbo.T1 (字段1,字段2,字段3) select 字段1,字段2,字段3 from DB2.dbo.T1 union all select 字段1,字段2,字段3 from DB3.dbo.T1 union all select 字段1,字段2,字段3 from DB4.dbo.T1 union all select 字段1,字段2,字段3 from DB5.dbo.T1 union all select 字段1,字段2,字段3 from DB6.dbo.T1 union all select 字段1,字段2,字段3 from DB7.dbo.T1 union all select 字段1,字段2,字段3 from DB7.dbo.T1 union all select 字段1,字段2,字段3 from DB8.dbo.T1 union all select 字段1,字段2,字段3 from DB9.dbo.T1 union all select 字段1,字段2,字段3 from DB10.dbo.T1
--union 合并重复行--union all 不合并重复行上述语句用的是union all,所以不合并重复行。你可以改为union。
insert into DB1.dbo.T1
select * from DB2.dbo.T1
union all
select * from DB3.dbo.T1
union all
select * from DB4.dbo.T1
union all
select * from DB5.dbo.T1
union all
select * from DB6.dbo.T1
union all
select * from DB7.dbo.T1
union all
select * from DB7.dbo.T1
union all
select * from DB8.dbo.T1
union all
select * from DB9.dbo.T1
union all
select * from DB10.dbo.T1--这个语句可以在这10个库中任意一个库中执行。
select 字段1,字段2,字段3 from DB2.dbo.T1
union all
select 字段1,字段2,字段3 from DB3.dbo.T1
union all
select 字段1,字段2,字段3 from DB4.dbo.T1
union all
select 字段1,字段2,字段3 from DB5.dbo.T1
union all
select 字段1,字段2,字段3 from DB6.dbo.T1
union all
select 字段1,字段2,字段3 from DB7.dbo.T1
union all
select 字段1,字段2,字段3 from DB7.dbo.T1
union all
select 字段1,字段2,字段3 from DB8.dbo.T1
union all
select 字段1,字段2,字段3 from DB9.dbo.T1
union all
select 字段1,字段2,字段3 from DB10.dbo.T1
或
数据库名..表名