1、我们可以先创建一个案例表。--创建A表create table table_A(name varchar(20))insert into table_Aselect 'liu'unionselect 'zhang'select * from table_A
2、通过复制表的方式，创建一个B表，输入脚本并执行。select top 0 * into table_B from table_Aselect * from table_B
3、同步实现的原理通过触发器进行实现，如果A表进行更新了，那么我们同时在B表进行表字段更新，同时进行B表的数据更新。使用下一步的脚本，建立一个A表的触发器，注意表名，进行修改。
4、--创建一个A表的触发器create trigger tr_table_a on table娄多骋能_afor insert,updateasbegin ---同步table_a表的列 de艘早祓胂clare @newcolname varchar(2000),@newtype varchar(2000),@newlength varchar(2000), @newprec varchar(2000),@newscale varchar(2000) declare @sql varchar(5000) set @sql = '' declare cur_col cursor for select B.name,C.name,B.length,B.prec,B.scale FROM sysobjects a left join syscolumns b on a.id = b.id left join systypes c on c.[xusertype] = b.[xusertype] where a.name = 'table_a' and b.name not in ( select b.name as typename FROM sysobjects a left join syscolumns b on a.id = b.id left join systypes c on c.[xusertype] = b.[xusertype] where a.name = 'table_b' --注意修改表名 ) open cur_col fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale while @@FETCH_STATUS = 0 begin if @newtype = 'varchar' or @newtype = 'char' begin set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newlength+')' --注意修改表名 end else begin set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newprec+','+@newscale+')' --注意修改表名 end exec(@sql) fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale end close cur_col deallocate cur_col --- 同步表数据 declare @name varchar(200) , @colid varchar(200) declare @str_colname varchar(4000) declare @sql_data varchar(5000) set @str_colname = '' select * into #tmp_inserted from inserted declare cur cursor for select b.name,colid FROM sysobjects a inner join syscolumns b on a.id = b.id where a.name = 'table_a' --注意修改表名 open cur fetch next from cur into @name,@colid while @@FETCH_STATUS = 0 begin set @str_colname = @str_colname + @name +', ' fetch next from cur into @name,@colid end close cur deallocate cur set @str_colname = left(@str_colname,len(@str_colname)-1) print @str_colname set @sql_data = ' insert into table_B ('+@str_colname+') select '+ @str_colname + ' from temp..#tmp_inserted' exec(@sql_data) end
5、建立好触发器，我们来测试给A表增加列，同时也给A表增加墙绅褡孛数据。看到B表中，是不是自动完成列的增加，数赍铈于脏据的增加--测试给A表增加字段addressalter table table_aadd address varchar(80)alter table table_aadd personnum numeric(12,2)
6、给A表写入数据---测试给A表增加数据insert into table_A (name,address,personnum)select 'tbc','yi huan 101',40000200
7、查看两个表的数据，验证触发器已经生效。 可以发现列已经增加完毕，数据也同步增加了。---检查两个表数据select * from Table_Aselect * from table_B
8、以下是测试完整的代码：--创建A表create table table_A(艘绒庳焰name varchar(20))insert i荏鱿胫协nto table_Aselect 'liu'unionselect 'zhang'select * from table_A---复制的方式创建B表select top 0 * into table_B from table_Aselect * from table_B--创建一个A表的触发器create trigger tr_table_a on table_afor insert,updateasbegin ---同步table_a表的列 declare @newcolname varchar(2000),@newtype varchar(2000),@newlength varchar(2000), @newprec varchar(2000),@newscale varchar(2000) declare @sql varchar(5000) set @sql = '' declare cur_col cursor for select B.name,C.name,B.length,B.prec,B.scale FROM sysobjects a left join syscolumns b on a.id = b.id left join systypes c on c.[xusertype] = b.[xusertype] where a.name = 'table_a' and b.name not in ( select b.name as typename FROM sysobjects a left join syscolumns b on a.id = b.id left join systypes c on c.[xusertype] = b.[xusertype] where a.name = 'table_b' --注意修改表名 ) open cur_col fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale while @@FETCH_STATUS = 0 begin if @newtype = 'varchar' or @newtype = 'char' begin set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newlength+')' --注意修改表名 end else begin set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newprec+','+@newscale+')' --注意修改表名 end exec(@sql) fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale end close cur_col deallocate cur_col --- 同步表数据 declare @name varchar(200) , @colid varchar(200) declare @str_colname varchar(4000) declare @sql_data varchar(5000) set @str_colname = '' select * into #tmp_inserted from inserted declare cur cursor for select b.name,colid FROM sysobjects a inner join syscolumns b on a.id = b.id where a.name = 'table_a' --注意修改表名 open cur fetch next from cur into @name,@colid while @@FETCH_STATUS = 0 begin set @str_colname = @str_colname + @name +', ' fetch next from cur into @name,@colid end close cur deallocate cur set @str_colname = left(@str_colname,len(@str_colname)-1) print @str_colname set @sql_data = ' insert into table_B ('+@str_colname+') select '+ @str_colname + ' from temp..#tmp_inserted' exec(@sql_data) end---测试语句--测试给A表增加字段addressalter table table_aadd address varchar(80)alter table table_aadd personnum numeric(12,2)---测试给A表增加数据insert into table_A (name,address,personnum)select 'tbc','yi huan 101',40000200---检查两个表数据select * from Table_Aselect * from table_B