SQL Server AB表同结构的简单数据同步存储过程

/*近日无事,写了一存储过程实现A,B同结构表的数据同步的简单处理的存储过程,欢迎大家丢砖.基本功能为同步insert AB的记录,最后以AB其中一表为准更新另外一表的差异记录.1.对于关键或者有些列为自增类型的,应该预先处理掉,这里留代各位大侠以后处理,好像是先建立一个去掉自增属性的临时表,然后导入原表数据,删除原表,再重新命表名2.对应是否需要关闭触发器,也未作处理.可以添加一个参数进行指示是否关闭触发器3.应考虑外键和约束.4.如AB表结构不一致的复杂度将大为增加,(实用性也将大大提高)呵呵,所以复杂的还是要考虑很多因素,不能简单的insert update了事.主要过程:1.从syscolumns,sysobjects,sysindexkeys表中获取表的字段信息2.构造insert同步语句3.构造update同步语句欢迎指出错误及纠正.*/CREATE Procedure tb_tab(@tab1 varchar(50),@tab2 varchar(50),@itab int)/*@tab1--A表表名@tab2--B表表名@itab--2:以B的数据更新A表 <>2:以A的数据更新B表*/asdeclare @sql varchar(2000);declare @sql_temp varchar(1000);declare @field_name varchar(50);declare @tabupdateto varchar(50);declare @tabupdatefrom varchar(50);--声明全部列,不用--declare cur_field1 cursor for--select syscolumns.name from syscolumns,sysobjects where syscolumns.id = sysobjects.id and sysobjects.name = @tb;/*在游标声明中无法使用变量,所以将游标要使用的变量先保存到临时表.实在没办法啦:).嘻嘻,可能我不知道别的办法.*/select @tab1 as c_tbn into #tbtmp;--声明关键字列游标declare cur_key1 cursor for select syscolumns.name from syscolumns,sysobjects,sysindexkeys,#tbtmp where syscolumns.id = sysobjects.id and sysobjects.name = #tbtmp.c_tbnand sysobjects.id = sysindexkeys.id and sysindexkeys.colid = syscolumns.colid and sysindexkeys.indid = 1;--声明关普通列游标declare cur_notkey_field cursor for select syscolumns.name from syscolumns,sysobjects,#tbtmp where syscolumns.id = sysobjects.id and sysobjects.name = #tbtmp.c_tbnand not exists (select * from sysindexkeys where sysobjects.id = sysindexkeys.id and sysindexkeys.colid = syscolumns.colid and sysindexkeys.indid = 1);/*insert into test1 select * from test2 where not exits (select * from test1 where test1.c_col1 = test2.c_col1 and test1.c_col2 = test2.c_col2)select test2.* from test2 where not EXISTS ( select * from test1 where test2.c_col1 = test1.c_col1 and test2.c_col2 = test1.c_col2)*/begin/*插入A表在B表中没有的数据--insert test1 select * from test2 where not EXISTS ( select * from test1 where test2.c_col1 = test1.c_col1 and test2.c_col2 = test1.c_col2)*/select @sql = 'insert into ' + @tab1 +' select * from ' + @tab2 + ' where not EXISTS ( select * from ' + @tab1 + ' where ';select @sql_temp = '';open cur_key1;--PRINT @@ERROR FETCH NEXT FROM cur_key1 into @field_name;--PRINT @field_name;--PRINT @@FETCH_STATUS while @@FETCH_STATUS = 0begin if @sql_temp <> '' begin select @sql_temp = @sql_temp + ' and '; end select @sql_temp = @sql_temp + @tab1 + '.' + @field_name + ' = ' + @tab2 + '.' + @field_name ; fetch next from cur_key1 into @field_name;endselect @sql_temp = @sql_temp + ')';select @sql = @sql + @sql_temp;close cur_key1; EXEC(@sql);--PRINT @@ERROR /*插入B表在A表中没有的数据--insert test2 select * from test1 where not EXISTS ( select * from test2 where test2.c_col1 = test1.c_col1 and test2.c_col2 = test1.c_col2)*/select @sql = 'insert into ' + @tab2 +' select * from ' + @tab1 + ' where not EXISTS ( select * from ' + @tab2 + ' where ';select @sql_temp = '';open cur_key1;fetch next from cur_key1 into @field_name;while @@FETCH_STATUS = 0begin if @sql_temp <> '' begin select @sql_temp = @sql_temp + ' and '; end select @sql_temp = @sql_temp + @tab2 + '.' + @field_name + ' = ' + @tab1 + '.' + @field_name ; fetch next from cur_key1 into @field_name;endselect @sql_temp = @sql_temp + ')';select @sql = @sql + @sql_temp;close cur_key1;exec(@sql);/*以指定表的数据为准,更新另外一个表的数据update test2 set test2.d_date = test1.d_date , test2.f_number = test1.f_numberfrom test1 where test2.c_col1 = test1.c_col1 and test2.c_col2 = test1.c_col2and ((test2.d_date <> test1.d_date or test1.d_date is null) or (test2.f_number <> test1.f_number or test1.f_number is null))*/select @tabupdatefrom = @tab1;select @tabupdateto = @tab2;if @itab = 2begin select @tabupdatefrom = @tab2; select @tabupdateto = @tab1;endselect @sql = 'update ' + @tabupdateto + ' set ';select @sql_temp = '(';open cur_notkey_field;fetch next from cur_notkey_field into @field_name;while @@FETCH_STATUS=0begin if @sql_temp <> '(' begin select @sql_temp = @sql_temp + ' or (' + @tabupdateto + '.' + @field_name + ' <> ' + @tabupdatefrom + '.' + @field_name + ' or ' + @tabupdatefrom + '.' + @field_name + ' is null or ' + @tabupdateto + '.' + @field_name + ' is null) '; end else begin select @sql_temp = @sql_temp + ' (' + @tabupdateto + '.' + @field_name + ' <> ' + @tabupdatefrom + '.' + @field_name + ' or ' + @tabupdatefrom + '.' + @field_name + ' is null or ' + @tabupdateto + '.' + @field_name + ' is null) '; end if @sql <> 'update ' + @tabupdateto + ' set ' begin select @sql = @sql + ' , '+ @tabupdateto + '.' + @field_name + ' = ' + @tabupdatefrom + '.' + @field_name; end else begin select @sql = @sql + @tabupdateto + '.' + @field_name + ' = ' + @tabupdatefrom + '.' + @field_name; end fetch next from cur_notkey_field into @field_name;endclose cur_notkey_field;deallocate cur_notkey_field;select @sql = @sql + ' from ' + @tabupdatefrom+ ' where ' + @sql_temp + ') and ';select @sql_temp = '( ';open cur_key1;fetch next from cur_key1 into @field_name;while @@FETCH_STATUS = 0begin if @sql_temp <> '( ' begin select @sql_temp = @sql_temp + ' and '; end select @sql_temp = @sql_temp + @tab2 + '.' + @field_name + ' = ' + @tab1 + '.' + @field_name; fetch next from cur_key1 into @field_name;endselect @sql_temp = @sql_temp + ')';select @sql = @sql + @sql_temp;close cur_key1;deallocate cur_key1;exec(@sql);--print @sqldrop table #tbtmp;end /*近日无事,写了一存储过程实现A,B同结构表的数据同步的简单处理的存储过程,欢迎大家丢砖.基