SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName 

WHERE (a.KeyName not in (select distinct b.KeyName From bug02)) 

ORDER BY a.KeyName asc


    

      　　　　   CREATE TABLE #p

	(      

		OrderID varchar(50), 

		primary key (OrderID)      

	);

	SET @temp_text = 'INSERT INTO #p '+@KeyText

	--PRINT @temp_text

	EXEC (@temp_text)	

	

	SET @KeyText = 'SELECT OrderID FROM #p'

	--如果一級(jí)表關(guān)聯(lián)的操作次數(shù)比較多那么可以訪源表操作 以臨時(shí)表取代物理表

	SET @SubKeyText = 'select 一級(jí)表_A_被關(guān)聯(lián)鍵 From 一級(jí)表_A with(nolock) where 一級(jí)表_A_關(guān)聯(lián)源表鍵 in (' + @KeyText+')'

	

	CREATE TABLE #q

	(      

		OrderID varchar(50), 

		primary key (OrderID)      

	);

	SET @temp_text = 'INSERT INTO #q '+@SubKeyText

	EXEC (@temp_text)	

	SET @SubKeyText ='SELECT OrderID FROM #q'

	

	--如果一級(jí)表關(guān)聯(lián)的操作次數(shù)不多可以直接生成數(shù)據(jù)過濾池

	SET @SubKeyTextforA ='select 一級(jí)表_B_被二級(jí)關(guān)聯(lián)鍵 From 一級(jí)表_B with(nolock) where 一級(jí)表_B_關(guān)聯(lián)源表鍵 in (' + @KeyText+')'

	SET @SubKeyTextforB ='select 一級(jí)表_C_被二級(jí)關(guān)聯(lián)鍵 From 一級(jí)表_C with(nolock) where 一級(jí)表_C_關(guān)聯(lián)源表鍵 in (' + @KeyText+')'

	

	--如果存在更多層操作在此處可以繼續(xù)關(guān)聯(lián)資源過濾池 Demo只做到三層

SET @THKeyTextforA ='select 二級(jí)表_A_被三級(jí)關(guān)聯(lián)鍵 From 二級(jí)表_A with(nolock) where 二級(jí)表_A_關(guān)聯(lián)一級(jí)表鍵 in (' + @SubKeyTextforA+')'
    

      --將錯(cuò)誤的批次訂單號(hào)入異常表

	Insert into 異常表(@ExTable) SELECT OrderID FROM #p

--@ExTable用來存放異常數(shù)據(jù) 如果當(dāng)期批次出錯(cuò) 則將本次批次訂單信息入庫(kù)@ExTable下一批次則過濾這些數(shù)據(jù)再執(zhí)行

	SET @KeyText = 'SELECT TOP '+CAST(@SynSize AS VARCHAR(10))+' '+@Base_Key+' FROM +
    

      '+@BaseTable+'+ WHERE '+@Base_Key+' not in (select '+@Base_Key+' From '+@ExTable+') ' 


    

      --一級(jí) 直接關(guān)聯(lián)源表主鍵 或?yàn)槎?jí)被關(guān)聯(lián)的主表

	INSERT INTO #k VALUES ('一級(jí)表_A',@Base_Key,@KeyText,'')					--一級(jí)表_A

	INSERT INTO #k VALUES ('一級(jí)表_B',@Base_Key,@KeyText,'')					--一級(jí)表_B

	INSERT INTO #k VALUES ('一級(jí)表_C',@Base_Key,@KeyText,'')					--一級(jí)表_C

--二級(jí) 規(guī)則間接關(guān)聯(lián)

	--@SubKeyText相關(guān)

	INSERT INTO #k VALUES ('二級(jí)表_A','二級(jí)表_A_關(guān)聯(lián)一級(jí)鍵',@SubKeyText,'')				--二級(jí)表_A

	INSERT INTO #k VALUES ('二級(jí)表_B','二級(jí)表_B_關(guān)聯(lián)一級(jí)鍵',@SubKeyText,'')				--二級(jí)表_B	

	INSERT INTO #k VALUES ('二級(jí)表_C','二級(jí)表_C_關(guān)聯(lián)一級(jí)鍵',@SubKeyText,'')				--二級(jí)表_C

--特殊處理 

	--自定義操作

	INSERT INTO #k VALUES ('特殊表','特殊表關(guān)聯(lián)鍵','自定義數(shù)據(jù)過濾方式','')			

	

	--其他 自增列處理

	--修改訂單,及其取消修改訂單狀態(tài)歷史表

	INSERT INTO #k VALUES ('自增表',@Base_Key,@KeyText,'自定義字段')


    

      DECLARE CUR_ORDERHEDER INSENSITIVE CURSOR FOR SELECT TableName,KeyName,temptext,colname FROM #k 

	OPEN CUR_ORDERHEDER

	FETCH CUR_ORDERHEDER INTO @Cur_Table,@Cur_Key,@Cur_W,@Cur_K

		WHILE @@FETCH_STATUS = 0

			BEGIN				

				 EXECUTE P_Task_Sub_Synchronization

				 @OutParam  = @OutParam OUT, @OutMessage = @OutMessage OUT,

			@KeyText =  @Cur_W,@Table= @Cur_Table,@Extension=@Extension,@IsDelSource=@IsDelSource,@KeyName=@Cur_Key,@ColName=@Cur_K

				 --SET @OutMessage = @OutMessage+@OutMessage

				 --PRINT @OutMessage

				 IF @OutParam <> 0  

					 BEGIN

						SET @OutMessage = @OutMessage + @Cur_Table +'操作失敗'						

						ROLLBACK TRAN

						--將錯(cuò)誤的批次訂單號(hào)入異常表

						Insert into 異常表(@ExTable) SELECT OrderID FROM #p

						DROP TABLE #k 

						DROP TABLE #p 

						DROP TABLE #q

						RETURN

					 END	

				 FETCH CUR_ORDERHEDER INTO @Cur_Table,@Cur_Key,@Cur_W,@Cur_K

			END

	ClOSE CUR_ORDERHEDER

	DEALLOCATE CUR_ORDERHEDER