Delete An SQL Server table connection

During data synchronization, a buddy wrote a cursor to recyclically update the table records in the database:

-- Following is incremental update

Declare @ Interfacecode_tmp Varchar ( 12 ), @ Affiliate_tmp Char ( 3 ), @ Dealernumber_tmp Varchar ( 5 ), @ Dspnumber_tmp Varchar ( 12 )

-- Process dealerauthorization 

declare dealerauth_cursor cursor for

SelectInterfacecode,Affiliate,DealernumberFromDealerauthorization_tmp

OpenDealerauth_cursor

fetch next from dealerauth_cursor into @ interfacecode_tmp , @ affiliate_tmp , @ dealernumber_tmp

While@ Fetch_status=0

Begin 

Delete Dealerauthorization Where Interfacecode = @ Interfacecode_tmp And Affiliate = @ Affiliate_tmp And Dealernumber = @ Dealernumber_tmp

Insert Into Dealerauthorization ( Interfacecode , Affiliate , Dealernumber , Isvalid , Expiredate )

select interfacecode , affiliate , dealernumber , isvalid , expiredate

From Dealerauthorization_tmp Where Interfacecode = @ Interfacecode_tmp And Affiliate = @ Affiliate_tmp And Dealernumber = @ Dealernumber_tmp

fetch next f rom dealerauth_cursor into @ interfacecode_tmp , @ affiliate_tmp , @ dealernumber_tmp

End

CloseDealerauth_cursor

DeallocateDealerauth_cursor

Nima, our database has more than 50 thousand pieces of data. I don't know how long it will take. Fortunately, the data is not big, and there are not many columns. He said it would take only one second to complete.

I think I wrote one before and helped him change it.

DeleteFromDealerauthorization

FromDealerauthorizationInnerJoinDealerauthorization_tmp B

On A . Dealernumber = B . Dealernumber And A . Affiliate = B . Affiliate And A . Interfacecode = B . Interfacecode

 

Insert Into Dealerauthorization ( Interfacecode , Affiliate , Dealernumber , Isvalid , Expiredate )

SelectInterfacecode,Affiliate,Dealernumber,Isvalid,Expiredate

FromDealerauthorization_tmp

It seems much more comfortable. Hey.