本文共 4393 字,大约阅读时间需要 14 分钟。
USE [LieBoLayout] GO /****** Object: StoredProcedure [etl_mid].[P_sync_etl_mid_data] Script Date: 01/28/2016 17:33:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [etl_mid].[P_sync_etl_mid_data] ( @source_db varchar(255), --来源数据库 @source_sch varchar(255), --来源架构 @source_table varchar(255),--来源表 @target_sch varchar(255), --目标架构 (需在当前库中) @target_table varchar(255),--目标表(没有的话,按照来源表自动创建) @key_col varchar(255), --键列 (用于来源表和目标表的关联,目前仅支持单一字段) @condition varchar(255)='', --限制条件 如限制7天内的数据参与更新。则可以写 create_date>=dateadd(dd,-7,getdate()) @is_new_table bit out ) AS begin set nocount on; --验证 declare @source varchar(255)=quotename(@source_db)+'.'+@source_sch+'.'+@source_table; declare @target varchar(255)=quotename(@target_sch)+'.'+@target_table; declare @nsql nvarchar(4000); if object_id(@source) is null begin RAISERROR('来源表为空',11,1); return; end if object_id(@target) is null begin --构建同步表 set @nsql= ' select top 0 * into '+@target+' from '+@source+' with(nolock) --添加索引和操作字段 create clustered index idx_'+@target_table+'_1 on '+@target+'('+quotename(@key_col)+'); --初装数据 insert into '+@target+' select * from '+@source+' with(nolock) ; alter table '+@target+' add sync_flag tinyint; print ''生成表'+@target+'成功!'' ' ; --print(@nsql); exec(@nsql); set @nsql='create nonclustered index idx_'+@target_table+'_2 on '+@target+'(sync_flag);'; exec(@nsql); set @is_new_table=1; RETURN; end --初始bi数据表,即数据填充至数据仓库/ods。 --增删改的标记。1 需要插入 2 需要更新,3 需要删除 存储过程? --判断顺序 清空字段-->更新-->删除-->插入 更新时需要判断空值条件,根据datatype填充值。 --清空 set @nsql= (case when len(@condition)>3 then ' delete '+@target+' where not ('+isnull(@condition,'')+');' else '' end) +' update '+@target+' set sync_flag=null where sync_flag is not null ; print ''表'+@target+'清除标记sync_flag成功!'' '; exec(@nsql); --循环判断更新()。 set @nsql= ' select name from sys.all_columns where object_id=object_id('''+@target+''') intersect select name from '+quotename(@source_db)+'.sys.all_columns where object_id=object_id('''+@source+''') '; declare @t_cols table(name varchar(255)); insert into @t_cols exec(@nsql); declare cu cursor for select ' update b set b.sync_flag=2,b.'+quotename(name)+'=a.'+quotename(name)+' from '+@source+' a join '+@target+' b on '+ (case when len(@condition)>3 then ' a.'+quotename(@key_col)+' in (select '+quotename(@key_col)+' from '+@source+' where '+isnull(@condition,'')+') and ' else '' end) +' a.'+quotename(@key_col)+'=b.'+quotename(@key_col)+' --b.sync_flag is null 不限制此处是为了更新数据 where isnull(a.'+quotename(name)+','+nullchar+')<>isnull(b.'+quotename(name)+','+nullchar+');' as sq from ( select a.name, (case when c.name in ('char','nchar','varchar','nvarchar') then '''''' when c.name in ('date','datetime','datetime2','smalldatetime','datetimeoffset') then '''1900-01-01''' when c.name in ('int','bigint','smallint','tinyint','float','decimal','bit','real','money','numeric','smallmoney') then '0' END) AS nullchar from @t_cols a join sys.all_columns b on b.object_id=object_id(@target)and a.name=b.name join sys.types c on b.user_type_id=c.user_type_id where a.name not in(@key_col) and c.name not in ('text','ntext','image','timestamp','binary','uniqueidentifier') ) as aa; open cu ; fetch next from cu into @nsql ; while @@fetch_status=0 begin exec(@nsql); fetch next from cu into @nsql ; end print '表'+@target+'标记sync_flag为2(表示更新)成功!'; close cu ; deallocate cu ; --需要删除的数据 set @nsql= ' update b set b.sync_flag=3 from '+@target+' b left join '+@source+' a on b.'+quotename(@key_col)+'=a.'+quotename(@key_col)+' '+ (case when len(@condition)>3 then 'and a.'+quotename(@key_col)+' in (select '+quotename(@key_col)+' from '+@source+' where '+isnull(@condition,'')+') ' else '' end) +' where a.'+quotename(@key_col)+' is null ; --b.sync_flag is null 不限制此处是为了更新数据 print ''表'+@target+'标记sync_flag为3(表示删除)成功!''; '; exec(@nsql); --需要插入的数据 set @nsql=stuff((select (','+quotename(name)) from @t_cols for xml path ('')),1,1,''); set @nsql= ' insert into '+@target+'('+@nsql+',sync_flag) select '+@nsql+',1 as sync_flag from '+@source+' a where '+ (case when len(@condition)>3 then ' a.'+quotename(@key_col)+' in (select '+quotename(@key_col)+' from '+@source+' where '+isnull(@condition,'')+') and 'else '' end)+' not exists(select 1 from '+@target+' b where b.'+quotename(@key_col)+'=a.'+quotename(@key_col)+'); --b.sync_flag is null 不限制此处是为了更新数据 print ''表'+@target+'标记sync_flag为1(表示插入)成功!''; '; exec(@nsql); END ;转载地址:http://qlfmi.baihongyu.com/