SQL SERVER 連結不同資料庫,做INSERT的動作,姓名個資做掩碼處理,

--連結不同資料庫,做INSERT的動作,個資做掩碼處理,錯誤時利用SQLMAIL發MAIL

declare @cc varchar(max);
declare @myCursor cursor;
set @myCursor =cursor fast_forward


FOR
--先找有重複的資料數目
select (SELECT (count(reg_rno))
 FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=999.999.999.999;User ID=@@@@@;Password=#####' ).[資料庫名稱].dbo.table_name where reg_rno is not null and len(reg_rno)>0 and lay_off=0 group by reg_rno having count(reg_rno)>1)

open @myCursor

 fetch next from @mycursor
 into @cc

 if ( @cc is null)
--沒有重複的資料

BEGIN
--做清空TABLE的動作

truncate table [資料庫名稱].dbo.TABLE_NAME ;

--把資料insert進去

insert into [資料庫名稱].dbo.TABLE_NAME (AG_CODE,AG_NAME,EMAIL,REMOVE_YN,CREATE_DT,CREATE_BY,MODIFY_DT,MODIFY_BY)
SELECT reg_rno
,case len(LTRIM(name))
when 1 then LTRIM(name)
when 2 then left(ltrim(name),1)+'O'
ELSE
left(ltrim(name),1)+REPLICATE('O',len(ltrim(name))-2)+right(ltrim(name),1)
END
,email,(case lay_off when 0 then 'N' when 1 then 'Y' end ),create_date ,cast(ISNULL(create_user,0) as varchar(20)) ,mdf_date ,cast(isnull(mdf_user,0) as varchar(20))
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=999.999.999.999;User ID=@@@@;Password=@@@@@' ).[資料庫名稱].dbo.table_name####### where reg_rno is not null and len(reg_rno)>0 and lay_off=0 ;

END
else
begin
--MAIL錯誤訊息

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMAIL設定的設定檔名稱',
@recipients = '###@######.com.tw;###@######.com.tw',
@body = '$$$$$$$$$$$$$$$$$$4',
@subject = 'XXXXXXXXXXXX' ;
end