- declare @tranNum table (Id int)
- insert into @tranNum values (400003), (7674714), (4122014), (94273), (4122020), (1350)
- ( SELECT 'Modified' as RowType
- ,[DIVISION]
- ,[STORENUM]
- ,[TRANSDATE]
- ,[TRANSNUM]
- ,[REGNUM]
- ,[TRANSTIME]
- ,[VERSION]
- ,[LINENUM]
- ,[RECTYPE]
- ,[STATUS]
- ,[BATCHNO]
- ,[CASHIERNUM]
- ,[EMPNUM]
- ,[TRANSSTAT]
- ,[TRANSTYPE]
- ,[EMPLOYEE_SALE_FLAG]
- ,[TRAINING_FLAG]
- ,[CODE]
- ,[PVTRANSCOUNT]
- ,[DATE_LOADED]
- ,[RECCODE]
- ,[VOID_CODE]
- ,[SALESPERSONNUM]
- ,[ATTRIBUTE_CHAR1]
- ,[REASON_TYPE]
- ,[ATTRIBUTE_CODE3]
- ,[ATTRIBUTE_CODE5]
- ,[OVERRIDE_AMOUNT]
- ,[LINE_DISCOUNT_AMOUNT]
- ,[EXTENDED_AMOUNT]
- ,[TRANS_DISCOUNT_AMOUNT]
- ,[TENDER_AMOUNT]
- ,[QUANTITY]
- ,[ORIG_STORENUM]
- ,[ORIG_TRANSDATE]
- ,[ORIG_AMOUNT]
- ,[ITEM]
- ,[MERCH_DEPT]
- ,[RETURN_FLAG]
- ,[CUSTOM_NUM1]
- ,[CUSTOM_NUM4]
- ,[CUSTOM_NUM7]
- ,[CUSTOM_CHAR2]
- ,[CUSTOM_CHAR3]
- ,[CUSTOM_CHAR4]
- ,[CUSTOM_CHAR5]
- ,[CUSTOM_CHAR6]
- ,[CUSTOM_CHAR7]
- ,[CUSTOM_DATE1]
- ,[TAX_AMOUNT]
- FROM [Balance].[dbo].[ADC_POS_DATA_TMP_1_BM_DEV] WHERE [TRANSNUM] in (select Id from @tranNum)
- EXCEPT
- SELECT 'Modified' as RowType
- ,[DIVISION]
- ,[STORENUM]
- ,[TRANSDATE]
- ,[TRANSNUM]
- ,[REGNUM]
- ,[TRANSTIME]
- ,[VERSION]
- ,[LINENUM]
- ,[RECTYPE]
- ,[STATUS]
- ,[BATCHNO]
- ,[CASHIERNUM]
- ,[EMPNUM]
- ,[TRANSSTAT]
- ,[TRANSTYPE]
- ,[EMPLOYEE_SALE_FLAG]
- ,[TRAINING_FLAG]
- ,[CODE]
- ,[PVTRANSCOUNT]
- ,[DATE_LOADED]
- ,[RECCODE]
- ,[VOID_CODE]
- ,[SALESPERSONNUM]
- ,[ATTRIBUTE_CHAR1]
- ,[REASON_TYPE]
- ,[ATTRIBUTE_CODE3]
- ,[ATTRIBUTE_CODE5]
- ,[OVERRIDE_AMOUNT]
- ,[LINE_DISCOUNT_AMOUNT]
- ,[EXTENDED_AMOUNT]
- ,[TRANS_DISCOUNT_AMOUNT]
- ,[TENDER_AMOUNT]
- ,[QUANTITY]
- ,[ORIG_STORENUM]
- ,[ORIG_TRANSDATE]
- ,[ORIG_AMOUNT]
- ,[ITEM]
- ,[MERCH_DEPT]
- ,[RETURN_FLAG]
- ,[CUSTOM_NUM1]
- ,[CUSTOM_NUM4]
- ,[CUSTOM_NUM7]
- ,[CUSTOM_CHAR2]
- ,[CUSTOM_CHAR3]
- ,[CUSTOM_CHAR4]
- ,[CUSTOM_CHAR5]
- ,[CUSTOM_CHAR6]
- ,[CUSTOM_CHAR7]
- ,[CUSTOM_DATE1]
- ,[TAX_AMOUNT]
- FROM [Balance].[dbo].[ACTIAN_TABLE_NAME]) WHERE [TRANSNUM] in (select Id from @tranNum) ----CHANGE TO GET ACTIAN TABLE NAME HERE
- UNION ALL
- ( SELECT 'Original' as RowType
- ,[DIVISION]
- ,[STORENUM]
- ,[TRANSDATE]
- ,[TRANSNUM]
- ,[REGNUM]
- ,[TRANSTIME]
- ,[VERSION]
- ,[LINENUM]
- ,[RECTYPE]
- ,[STATUS]
- ,[BATCHNO]
- ,[CASHIERNUM]
- ,[EMPNUM]
- ,[TRANSSTAT]
- ,[TRANSTYPE]
- ,[EMPLOYEE_SALE_FLAG]
- ,[TRAINING_FLAG]
- ,[CODE]
- ,[PVTRANSCOUNT]
- ,[DATE_LOADED]
- ,[RECCODE]
- ,[VOID_CODE]
- ,[SALESPERSONNUM]
- ,[ATTRIBUTE_CHAR1]
- ,[REASON_TYPE]
- ,[ATTRIBUTE_CODE3]
- ,[ATTRIBUTE_CODE5]
- ,[OVERRIDE_AMOUNT]
- ,[LINE_DISCOUNT_AMOUNT]
- ,[EXTENDED_AMOUNT]
- ,[TRANS_DISCOUNT_AMOUNT]
- ,[TENDER_AMOUNT]
- ,[QUANTITY]
- ,[ORIG_STORENUM]
- ,[ORIG_TRANSDATE]
- ,[ORIG_AMOUNT]
- ,[ITEM]
- ,[MERCH_DEPT]
- ,[RETURN_FLAG]
- ,[CUSTOM_NUM1]
- ,[CUSTOM_NUM4]
- ,[CUSTOM_NUM7]
- ,[CUSTOM_CHAR2]
- ,[CUSTOM_CHAR3]
- ,[CUSTOM_CHAR4]
- ,[CUSTOM_CHAR5]
- ,[CUSTOM_CHAR6]
- ,[CUSTOM_CHAR7]
- ,[CUSTOM_DATE1]
- ,[TAX_AMOUNT]
- FROM [Balance].[dbo].[ACTIAN_TABLE_NAME]) WHERE [TRANSNUM] in (select Id from @tranNum) ----CHANGE TO GET ACTIAN TABLE NAME HERE
- EXCEPT
- SELECT 'Original' as RowType
- ,[DIVISION]
- ,[STORENUM]
- ,[TRANSDATE]
- ,[TRANSNUM]
- ,[REGNUM]
- ,[TRANSTIME]
- ,[VERSION]
- ,[LINENUM]
- ,[RECTYPE]
- ,[STATUS]
- ,[BATCHNO]
- ,[CASHIERNUM]
- ,[EMPNUM]
- ,[TRANSSTAT]
- ,[TRANSTYPE]
- ,[EMPLOYEE_SALE_FLAG]
- ,[TRAINING_FLAG]
- ,[CODE]
- ,[PVTRANSCOUNT]
- ,[DATE_LOADED]
- ,[RECCODE]
- ,[VOID_CODE]
- ,[SALESPERSONNUM]
- ,[ATTRIBUTE_CHAR1]
- ,[REASON_TYPE]
- ,[ATTRIBUTE_CODE3]
- ,[ATTRIBUTE_CODE5]
- ,[OVERRIDE_AMOUNT]
- ,[LINE_DISCOUNT_AMOUNT]
- ,[EXTENDED_AMOUNT]
- ,[TRANS_DISCOUNT_AMOUNT]
- ,[TENDER_AMOUNT]
- ,[QUANTITY]
- ,[ORIG_STORENUM]
- ,[ORIG_TRANSDATE]
- ,[ORIG_AMOUNT]
- ,[ITEM]
- ,[MERCH_DEPT]
- ,[RETURN_FLAG]
- ,[CUSTOM_NUM1]
- ,[CUSTOM_NUM4]
- ,[CUSTOM_NUM7]
- ,[CUSTOM_CHAR2]
- ,[CUSTOM_CHAR3]
- ,[CUSTOM_CHAR4]
- ,[CUSTOM_CHAR5]
- ,[CUSTOM_CHAR6]
- ,[CUSTOM_CHAR7]
- ,[CUSTOM_DATE1]
- ,[TAX_AMOUNT]
- FROM [Balance].[dbo].[ADC_POS_DATA_TMP_1_BM_DEV]) WHERE [TRANSNUM] in (select Id from @tranNum)