C11/System/OPTIMA/SQL/batch/VPP61.bat

@echo off
call "C:\GlobalCube\Tasks\scripts\config2.bat"
rem ==VPP61==
del C:\GlobalCube\System\OPTIMA\SQL\logs\VPP61*.* /Q /F >nul 2>nul

if not "%1"=="" goto :increment

:full
  call sql_query.bat "TRUNCATE TABLE [OPTIMA].[import].[VPP61]"
rem Nur in Quelle: LEXUS_DEALER;BRANCH_NO_2;FIAT_DEALER;DACIA_DEALER
  call bcp_queryout.bat "VPP61_1" "SELECT T1.[STOCK], T1.[STATE_CODE_PARAM], T1.[TRANSACT_DATE], T1.[HANDLER], T1.[STOCK_NAME], T1.[STOCK_INTEREST], T1.[SERVICE_FACTOR_ALPHA], T1.[SERVICE_FACTOR], T1.[BEH_CHECK], T1.[LOCKED], T1.[LOCKED_TEXT], T1.[LOCKED_TEXT1], T1.[LOCKED_TEXT2], T1.[ZERO_QNT_LIST], T1.[NOCARD_ON_ZERO_LIST], T1.[TOYOTA_DEALER], T1.[VOLVO_DEALER], T1.[CALC_MIN_IN_STOCK], T1.[FORD_DEALER], T1.[GOD_DEALER], T1.[UDAS_HANDLER], T1.[BRANCH_NO], T1.[FORD_STOCK_ID], T1.[FORD_PART_DEALER], T1.[PSA_F_DEALER], T1.[NCG_DEALER], T1.[RENAULT_DEALER], T1.[VO_DEALER], T1.[OPEL_COMM_DEALER], T1.[OPEL_DEALER], T1.[USE_SECTIONS], T1.[USE_TOTAL_CHECK], T1.[USE_FIRST_PART_CHECK], T1.[USE_RE_COUNT], T1.[FREEZE], T1.[FORM_STOCK_VARIANCE], T1.[FORM_INVENTORY], T1.[FUNC_STOCK_CHECK], T1.[LINES_PER_SECTION], T1.[DIFF_LIST_WANTED], T1.[BLOCK_NEGATIVE_STOCK], T1.[LIST_NO_SERIES], T1.[DUMMY_LIST_NO_SERIES], T1.[LIST_EXT_NO_SERIES], T1.[EXT_JOB_NO_SERIES], T1.[APPROVE_BEFORE_INTAKE], T1.[BACK_ORDER_PERM], T1.[IV_SUB_DEALER_NO], T1.[SPEC_PURCH_PRICE], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as [Client_DB] FROM [deop01].[dbo].[vPP61] T1  WHERE 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VPP61_1" "[import].[VPP61]" "OPTIMA"
rem Nur in Quelle: LEXUS_DEALER;BRANCH_NO_2;FIAT_DEALER;DACIA_DEALER
  call bcp_queryout.bat "VPP61_2" "SELECT T1.[STOCK], T1.[STATE_CODE_PARAM], T1.[TRANSACT_DATE], T1.[HANDLER], T1.[STOCK_NAME], T1.[STOCK_INTEREST], T1.[SERVICE_FACTOR_ALPHA], T1.[SERVICE_FACTOR], T1.[BEH_CHECK], T1.[LOCKED], T1.[LOCKED_TEXT], T1.[LOCKED_TEXT1], T1.[LOCKED_TEXT2], T1.[ZERO_QNT_LIST], T1.[NOCARD_ON_ZERO_LIST], T1.[TOYOTA_DEALER], T1.[VOLVO_DEALER], T1.[CALC_MIN_IN_STOCK], T1.[FORD_DEALER], T1.[GOD_DEALER], T1.[UDAS_HANDLER], T1.[BRANCH_NO], T1.[FORD_STOCK_ID], T1.[FORD_PART_DEALER], T1.[PSA_F_DEALER], T1.[NCG_DEALER], T1.[RENAULT_DEALER], T1.[VO_DEALER], T1.[OPEL_COMM_DEALER], T1.[OPEL_DEALER], T1.[USE_SECTIONS], T1.[USE_TOTAL_CHECK], T1.[USE_FIRST_PART_CHECK], T1.[USE_RE_COUNT], T1.[FREEZE], T1.[FORM_STOCK_VARIANCE], T1.[FORM_INVENTORY], T1.[FUNC_STOCK_CHECK], T1.[LINES_PER_SECTION], T1.[DIFF_LIST_WANTED], T1.[BLOCK_NEGATIVE_STOCK], T1.[LIST_NO_SERIES], T1.[DUMMY_LIST_NO_SERIES], T1.[LIST_EXT_NO_SERIES], T1.[EXT_JOB_NO_SERIES], T1.[APPROVE_BEFORE_INTAKE], T1.[BACK_ORDER_PERM], T1.[IV_SUB_DEALER_NO], T1.[SPEC_PURCH_PRICE], T1.[timestamp], T1.[UNIQUE_IDENT], '2' as [Client_DB] FROM [deop02].[dbo].[vPP61] T1  WHERE 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VPP61_2" "[import].[VPP61]" "OPTIMA"
  goto :cleanup

:increment
  call sql_query.bat "TRUNCATE TABLE [OPTIMAX].[temp].[VPP61]"

  call sql_timestamp.bat "VPP61_1" "[OPTIMA].[import].[VPP61]" "1"
  call bcp_queryout.bat "VPP61_1" "SELECT T1.[STOCK], T1.[STATE_CODE_PARAM], T1.[TRANSACT_DATE], T1.[HANDLER], T1.[STOCK_NAME], T1.[STOCK_INTEREST], T1.[SERVICE_FACTOR_ALPHA], T1.[SERVICE_FACTOR], T1.[BEH_CHECK], T1.[LOCKED], T1.[LOCKED_TEXT], T1.[LOCKED_TEXT1], T1.[LOCKED_TEXT2], T1.[ZERO_QNT_LIST], T1.[NOCARD_ON_ZERO_LIST], T1.[TOYOTA_DEALER], T1.[VOLVO_DEALER], T1.[CALC_MIN_IN_STOCK], T1.[FORD_DEALER], T1.[GOD_DEALER], T1.[UDAS_HANDLER], T1.[BRANCH_NO], T1.[FORD_STOCK_ID], T1.[FORD_PART_DEALER], T1.[PSA_F_DEALER], T1.[NCG_DEALER], T1.[RENAULT_DEALER], T1.[VO_DEALER], T1.[OPEL_COMM_DEALER], T1.[OPEL_DEALER], T1.[USE_SECTIONS], T1.[USE_TOTAL_CHECK], T1.[USE_FIRST_PART_CHECK], T1.[USE_RE_COUNT], T1.[FREEZE], T1.[FORM_STOCK_VARIANCE], T1.[FORM_INVENTORY], T1.[FUNC_STOCK_CHECK], T1.[LINES_PER_SECTION], T1.[DIFF_LIST_WANTED], T1.[BLOCK_NEGATIVE_STOCK], T1.[LIST_NO_SERIES], T1.[DUMMY_LIST_NO_SERIES], T1.[LIST_EXT_NO_SERIES], T1.[EXT_JOB_NO_SERIES], T1.[APPROVE_BEFORE_INTAKE], T1.[BACK_ORDER_PERM], T1.[IV_SUB_DEALER_NO], T1.[SPEC_PURCH_PRICE], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as [Client_DB] FROM [deop01].[dbo].[vPP61] T1  WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VPP61_1" "[temp].[VPP61]" "OPTIMAX"

  call sql_timestamp.bat "VPP61_2" "[OPTIMA].[import].[VPP61]" "2"
  call bcp_queryout.bat "VPP61_2" "SELECT T1.[STOCK], T1.[STATE_CODE_PARAM], T1.[TRANSACT_DATE], T1.[HANDLER], T1.[STOCK_NAME], T1.[STOCK_INTEREST], T1.[SERVICE_FACTOR_ALPHA], T1.[SERVICE_FACTOR], T1.[BEH_CHECK], T1.[LOCKED], T1.[LOCKED_TEXT], T1.[LOCKED_TEXT1], T1.[LOCKED_TEXT2], T1.[ZERO_QNT_LIST], T1.[NOCARD_ON_ZERO_LIST], T1.[TOYOTA_DEALER], T1.[VOLVO_DEALER], T1.[CALC_MIN_IN_STOCK], T1.[FORD_DEALER], T1.[GOD_DEALER], T1.[UDAS_HANDLER], T1.[BRANCH_NO], T1.[FORD_STOCK_ID], T1.[FORD_PART_DEALER], T1.[PSA_F_DEALER], T1.[NCG_DEALER], T1.[RENAULT_DEALER], T1.[VO_DEALER], T1.[OPEL_COMM_DEALER], T1.[OPEL_DEALER], T1.[USE_SECTIONS], T1.[USE_TOTAL_CHECK], T1.[USE_FIRST_PART_CHECK], T1.[USE_RE_COUNT], T1.[FREEZE], T1.[FORM_STOCK_VARIANCE], T1.[FORM_INVENTORY], T1.[FUNC_STOCK_CHECK], T1.[LINES_PER_SECTION], T1.[DIFF_LIST_WANTED], T1.[BLOCK_NEGATIVE_STOCK], T1.[LIST_NO_SERIES], T1.[DUMMY_LIST_NO_SERIES], T1.[LIST_EXT_NO_SERIES], T1.[EXT_JOB_NO_SERIES], T1.[APPROVE_BEFORE_INTAKE], T1.[BACK_ORDER_PERM], T1.[IV_SUB_DEALER_NO], T1.[SPEC_PURCH_PRICE], T1.[timestamp], T1.[UNIQUE_IDENT], '2' as [Client_DB] FROM [deop02].[dbo].[vPP61] T1  WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VPP61_2" "[temp].[VPP61]" "OPTIMAX"

  call sql_query.bat "DELETE T1 FROM [OPTIMA].[import].[VPP61] T1 INNER JOIN [OPTIMAX].[temp].[VPP61] T2 ON T1.[CLIENT_DB] = T2.[CLIENT_DB] AND T1.[STOCK] = T2.[STOCK]"
  call sql_query.bat "INSERT INTO [OPTIMA].[import].[VPP61] with (TABLOCK) SELECT * FROM [OPTIMAX].[temp].[VPP61] T1"

:cleanup
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\VPP61_1.csv"
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\VPP61_2.csv"