MSCRM 删除客户数据

declare @i int=1
declare @tmpTable table(Id uniqueidentifier primary key)


delete dbo.OpportunityExtensionBase
delete dbo.OpportunityProductExtensionBase
delete dbo.OpportunityProductBase
delete dbo.OpportunityCloseBase
delete dbo.OpportunityCompetitors
delete dbo.QuoteDetailExtensionBase
delete dbo.QuoteDetailBase
delete dbo.QuoteExtensionBase
delete dbo.QuoteBase
delete dbo.SalesOrderDetailExtensionBase
delete dbo.SalesOrderDetailBase
delete dbo.SalesOrderExtensionBase
delete dbo.SalesOrderBase
delete dbo.InvoiceDetailExtensionBase
delete dbo.InvoiceDetailBase
delete dbo.InvoiceExtensionBase
delete dbo.InvoiceBase
delete dbo.OpportunityBase

delete dbo.PhoneCallExtensionBase
delete dbo.PhoneCallBase

delete dbo.TaskExtensionBase
delete dbo.TaskBase

delete dbo.LetterExtensionBase
delete dbo.LetterBase

delete dbo.LeadProduct
delete dbo.LeadExtensionBase

delete dbo.LeadCompetitors
delete dbo.LeadAddressBase
delete dbo.LeadBase

delete dbo.FaxExtensionBase
delete dbo.FaxBase

delete dbo.EmailHashBase
delete dbo.EmailExtensionBase
delete dbo.EmailBase

truncate table dbo.CustomerAddressExtensionBase
--declare @i int=1,@id uniqueidentifier
--declare @tmpTable table(Id uniqueidentifier)
set @i=1
while @i>0
begin    
    insert into @tmpTable
    select top 20000 CustomerAddressId from dbo.CustomerAddressBase
    set @i=@@ROWCOUNT
    delete from dbo.CustomerAddressBase where CustomerAddressId in(
        select [Id] from @tmpTable
    )
    
    delete @tmpTable
end

delete dbo.AppointmentExtensionBase
delete dbo.AppointmentBase

--declare @i int=1,@id uniqueidentifier
--declare @tmpTable table(Id uniqueidentifier)
set @i=1
while @i>0
begin    
    insert into @tmpTable
    select top 20000 ActivityId from dbo.AppointmentBase
    set @i=@@ROWCOUNT
    delete from dbo.AppointmentBase where ActivityId in(
        select [Id] from @tmpTable
    )    
    delete @tmpTable
end


delete dbo.BulkOperationLogBase
delete dbo.BulkOperationBase

delete dbo.ServiceAppointmentBase

delete dbo.CampaignActivityItemBase
delete dbo.CampaignActivityBase
delete dbo.CampaignActivityBase

delete dbo.ActivityPointerBase
delete dbo.ActivityPartyBase

delete dbo.ContactExtensionBase
--declare @i int=1,@id uniqueidentifier
--declare @tmpTable table(Id uniqueidentifier)
set @i=1
while @i>0
begin    
    insert into @tmpTable
    select top 20000 ContactId from dbo.ContactBase
    set @i=@@ROWCOUNT
    delete from dbo.ContactBase where ContactId in(
        select [Id] from @tmpTable
    )    
    delete @tmpTable
end


delete dbo.AccountLeads

truncate table dbo.AccountExtensionBase
--declare @i int=1,@id uniqueidentifier
--declare @tmpTable table(Id uniqueidentifier)
set @i=1
while @i>0
begin    
    insert into @tmpTable
    select top 20000 AccountId from dbo.AccountBase
    set @i=@@ROWCOUNT
    delete from dbo.AccountBase where AccountId in(
        select [Id] from @tmpTable
    )    
    delete @tmpTable
end