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表 */ as declare @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_tbn and 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_tbn and 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 = 0 begin 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; end select @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 = 0 begin 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; end select @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_number from test1 where test2.c_col1 = test1.c_col1 and test2.c_col2 = test1.c_col2 and ((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 = 2 begin select @tabupdatefrom = @tab2; select @tabupdateto = @tab1; end select @sql = 'update ' + @tabupdateto + ' set '; select @sql_temp = '('; open cur_notkey_field; fetch next from cur_notkey_field into @field_name; while @@FETCH_STATUS=0 begin 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; end close 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 = 0 begin 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; end select @sql_temp = @sql_temp + ')'; select @sql = @sql + @sql_temp; close cur_key1; deallocate cur_key1; exec(@sql); --print @sql drop table #tbtmp; end