sqlserver增量更新另一个表的缺失数据

declare @tname varchar(100),@tid int declare cur_table cursor for select [name],[object_id] from sys.objects where [type]='u' open cur_table fetch next from cur_table into @tname,@tid while @@fetch_status=0 begin ?? ?declare @sql varchar(500),@pri_key varchar(50) ?? ?declare @str varchar(8000),@condition varchar(500) ?? ?set @str='' ?? ?set @condition='' ?? ? ?? ?select @str=[name]+','+@str from sys.columns where [object_id]=@tid ?? ?and [name] not in(select [name] from syscolumns where xtype=189) ?? ?and [name] not in(select [name] from sys.columns where is_identity=1) ?? ?order by column_id desc ?? ?set @str=substring(@str,1,len(@str)-1)?? ? ?? ? ?? ?select top 1 @condition=' and '+column_name+' not in (select '+column_name+' from ?? ?'+@tname+' where 1=1' from information_schema.key_column_usage ?? ?where constraint_name like 'pk%' and table_name=(''+@tname+'') ?? ?order by column_name asc ?? ?print @condition ?? ? ?? ?declare @c2 varchar(500),@pri_count int ?? ?set @c2='' ?? ?select @pri_count=isnull(count(*),0) from information_schema.key_column_usage ?? ?where constraint_name like 'pk%' and table_name=(''+@tname+'')?? ? ?? ?if @pri_count>1 ?? ?begin ?? ???? select top (@pri_count-1) @c2=' and '+column_name+'=b.'+column_name +@c2 ?? ???? from information_schema.key_column_usage ?? ???? where constraint_name like 'pk%' and table_name=(''+@tname+'')?? ? ?? ???? order by column_name desc ?? ?end ?? ?print @c2 ?? ?set @condition=@condition+@c2+')' ?? ???? ?? ?if exists(select 1 from test..sysobjects where [name]=(''+@tname+'')) ?? ?begin ?? ???? set @sql='insert into '+@tname+'('+@str+') ?? ???? ??? ??? ? select '+@str+' from test..'+@tname+' b ?? ???? ??? ??? ? where 1=1'+@condition ?? ???? print @sql ?? ???? --exec(@sql) ?? ?end ?? ?fetch next from cur_table into @tname,@tid end close cur_table DEALLOCATE cur_table go

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!