Insert into 库名..整合表名 Select * from 库名1..a union Select * from 库名2..b
--使用游标逐个更新表,如果表中有使用自增长类型的字段,有可能关联就出错了,建议使用GUIDdeclare @TableName varchar(100) declare @sql varchar(8000)declare tables cursor for SELECT name FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0open tablesfetch next from tables into @TableNamewhile @@FETCH_STATUS=0 begin set @sql = 'insert into [数据库a]..' + @TableName + ' select * from [数据库b]..' + @TableName + ' where 主键 not in (select 主键 from [数据库b]..' + @TableName + ')' exec @sql set @sql = 'insert into [数据库b]..' + @TableName + ' select * from [数据库a]..' + @TableName + ' where 主键 not in (select 主键 from [数据库a]..' + @TableName + ')' exec @sql FETCH NEXT FROM tables INTO @TableName ENDClose tables DEALLOCATE tables
接分 begin set @sql = 'insert into [数据库a]..' + @TableName + ' select * from [数据库b]..' + @TableName + ' where 主键 not in (select 主键 from [数据库b]..' + @TableName + ')' exec @sql set @sql = 'insert into [数据库b]..' + @TableName + ' select * from [数据库a]..' + @TableName + ' where 主键 not in (select 主键 from [数据库a]..' + @TableName + ')' exec @sql FETCH NEXT FROM tables INTO @TableName END
Select * from 库名1..a
union
Select * from 库名2..b
declare @sql varchar(8000)declare tables cursor for
SELECT name FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0open tablesfetch next from tables into @TableNamewhile @@FETCH_STATUS=0
begin
set @sql = 'insert into [数据库a]..' + @TableName + '
select * from [数据库b]..' + @TableName + ' where 主键 not in (select 主键 from [数据库b]..' + @TableName + ')'
exec @sql
set @sql = 'insert into [数据库b]..' + @TableName + '
select * from [数据库a]..' + @TableName + ' where 主键 not in (select 主键 from [数据库a]..' + @TableName + ')'
exec @sql
FETCH NEXT FROM tables INTO @TableName
ENDClose tables
DEALLOCATE tables
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'shuju.categories' 无效。
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'northwind.catagories' 无效。
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'northwind.categories' 无效。
begin
set @sql = 'insert into [数据库a]..' + @TableName + '
select * from [数据库b]..' + @TableName + ' where 主键 not in (select 主键 from [数据库b]..' + @TableName + ')'
exec @sql
set @sql = 'insert into [数据库b]..' + @TableName + '
select * from [数据库a]..' + @TableName + ' where 主键 not in (select 主键 from [数据库a]..' + @TableName + ')'
exec @sql
FETCH NEXT FROM tables INTO @TableName
END