SQL Server 表串連刪除

一哥們在做資料同步的時候寫了個遊標去迴圈更新資料庫裡面的表記錄：

-- 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 CURSORFOR

SELECT InterfaceCode,Affiliate,DealerNumber FROM DealerAuthorization_tmp

OPEN dealerAuth_cursor

FETCHNEXTFROM 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 NEXTF ROM dealerAuth_cursor INTO @InterfaceCode_tmp,@Affiliate_tmp,@DealerNumber_tmp

END

CLOSE dealerAuth_cursor

DEALLOCATE dealerAuth_cursor

尼瑪，我們資料庫有5萬多條資料，我不知道要執行多久。還好資料都不是很大，欄位也不多，他說只用一秒就完了。

想起以前寫過一個，幫他改了下

DELETE　　FROM DealerAuthorization

FROM　　DealerAuthorization a inner join DealerAuthorization_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)

SELECT　　InterfaceCode, Affiliate, DealerNumber, isValid,ExpireDate

FROM　　DealerAuthorization_tmp

看起來舒服多了。嘿嘿。