P_sync_etl_mid_data

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
;