|
,billing_address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,parent_id,sic_code,campaign_id from sugarcrm642cebackup.accountsbackup where ISNULL(ExecutingState) and BehaviorType=''I''')
insert into account( id ,name, date_entered,date_modified ,modified_user_id, created_by,[description] ,deleted, assigned_user_id,account_type ,industry, annual_revenue,phone_fax ,billing_address_street, billing_address_city,billing_address_state ,billing_address_postalcode, billing_address_country,rating ,phone_office, phone_alternate,website ,[ownership], employees,ticker_symbol ,shipping_address_street, shipping_address_city,shipping_address_state ,shipping_address_postalcode, shipping_address_country,parent_id ,sic_code, campaign_id)
select * from openquery(MySql , 'select rtrim(id), name,date_entered,date_modified,rtrim(modified_user_id),rtrim(created_by),description,deleted,rtrim(assigned_user_id),account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,rtrim(parent_id),sic_code,rtrim(campaign_id) from sugarcrm642cebackup.accountsbackup where ISNULL(ExecutingState) and BehaviorType=''I''')
update openquery (MySql, 'select * from sugarcrm642cebackup.accountsbackup where ISNULL(ExecutingState) and BehaviorType=''i''') set ExecutingState='D' ,ModificationTime= getdate()
--删除BehaviorState为U的记录
if object_id ('tempdb..#temp') is not null Begin
drop table #temp
End
select * into #temp from openquery( MySql, 'select rtrim(id),name from sugarcrm642cebackup.accountsbackup where ISNULL(ExecutingState) and BehaviorType=''D''')
declare @count int --变量定义
set @count =(select count(*) from #temp );
if @count >0 begin
delete from account where id in (select rtrim(id ) from #temp)
update openquery (MySql, 'select * from sugarcrm642cebackup.accountsbackup where ISNULL(ExecutingState) and BehaviorType=''D''') set ExecutingState='D' ,ModificationTime= getdate()
End
--更新BehaviorState为D的记录
if object_id ('tempdb..#temp1') is not null Begin
drop table #temp1
End
select * into #temp1 from openquery( MySql, 'select rtrim(id) id, name,date_entered,date_modified,rtrim(modified_user_id) modified_user_id,rtrim(created_by) created_by,description,deleted,rtrim(assigned_user_id) assigned_user_id,account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,rtrim(parent_id) parent_id,sic_code,rtrim(campaign_id) campaign_id from s |