CLS ECHO ************************************************************************************ ECHO * ECHO * Step 5.2: Creating the Upsert Statement ECHO * ECHO ************************************************************************************ ECHO .SET ERROROUT STDOUT >> InsUpd.bteq ECHO .RUN FILE logon.txt >> InsUpd.bteq ECHO .SET DEFAULTS >> InsUpd.bteq ECHO .SET FORMAT OFF >> InsUpd.bteq ECHO .SET FOLDLINE OFF >> InsUpd.bteq ECHO .SET SIDETITLES OFF >> InsUpd.bteq ECHO .SET WIDTH 254 >> InsUpd.bteq ECHO UPDATE %1.%2 > Ins_Upd.txt ECHO SET >> Ins_Upd.txt ECHO .EXPORT REPORT FILE = Ins_Upd.txt; >> InsUpd.bteq ECHO SEL DER_1.T (TITLE '') >> InsUpd.bteq ECHO FROM >> InsUpd.bteq ECHO (SEL >> InsUpd.bteq ECHO MAX(ColumnPosition) OVER(PARTITION BY DatabaseName, TableName ORDER BY ColumnPosition) AS MaxPos >> InsUpd.bteq ECHO ,CASE WHEN ColumnPosition = 1 AND ColumnPosition = MaxPos AND IndexType ^<^> 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName)>> InsUpd.bteq ECHO WHEN ColumnPosition = 1 AND ColumnPosition ^< MaxPos AND IndexType ^<^> 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| ',' >> InsUpd.bteq ECHO WHEN ColumnPosition ^< MaxPos AND IndexType ^<^> 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| ',' >> InsUpd.bteq ECHO WHEN ColumnPosition = MaxPos AND IndexType ^<^> 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| '' >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO END AS t >> InsUpd.bteq ECHO FROM (SEL >> InsUpd.bteq ECHO T1.DatabaseName >> InsUpd.bteq ECHO ,T1.TableName >> InsUpd.bteq ECHO ,T1.ColumnName >> InsUpd.bteq ECHO ,COALESCE(IndexType,'X') AS IndexType >> InsUpd.bteq ECHO ,ROW_NUMBER() OVER(PARTITION BY T1.DatabaseName, T1.TableName ORDER BY ColumnID) AS ColumnPosition >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO FROM DBC.Columns T1 LEFT JOIN DBC.Indices T2 >> InsUpd.bteq ECHO ON T1.DatabaseName = T2.DatabaseName >> InsUpd.bteq ECHO AND T1.TableName = T2.TableName >> InsUpd.bteq ECHO AND T1.ColumnName = T2.ColumnName >> InsUpd.bteq ECHO WHERE T1.DatabaseName = '%1' >> InsUpd.bteq ECHO AND T1.TableName = '%2') AS DER >> InsUpd.bteq ECHO QUALIFY T IS NOT NULL) DER_1; >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO SEL DER_1.T (TITLE '') >> InsUpd.bteq ECHO FROM >> InsUpd.bteq ECHO (SEL >> InsUpd.bteq ECHO MAX(ColumnPosition) OVER(PARTITION BY DatabaseName, TableName ORDER BY ColumnPosition) AS MaxPos >> InsUpd.bteq ECHO ,CASE WHEN ColumnPosition = 1 AND ColumnPosition = MaxPos AND IndexType = 'P' THEN 'WHERE ' ^|^| TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| ';' >> InsUpd.bteq ECHO WHEN ColumnPosition = 1 AND ColumnPosition ^< MaxPos AND IndexType = 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| ' AND ' >> InsUpd.bteq ECHO WHEN ColumnPosition ^< MaxPos AND IndexType = 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| ' AND' >> InsUpd.bteq ECHO WHEN ColumnPosition = MaxPos AND IndexType = 'P' THEN TRIM(ColumnName) ^|^| ' = ' ^|^| ':' ^|^| TRIM(ColumnName) ^|^| ';' >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO END AS t >> InsUpd.bteq ECHO ,ColumnPosition >> InsUpd.bteq ECHO FROM (SEL >> InsUpd.bteq ECHO T1.DatabaseName >> InsUpd.bteq ECHO ,T1.TableName >> InsUpd.bteq ECHO ,T1.ColumnName >> InsUpd.bteq ECHO ,COALESCE(IndexType,'X') AS IndexType >> InsUpd.bteq ECHO ,ROW_NUMBER() OVER(PARTITION BY T1.DatabaseName, T1.TableName ORDER BY ColumnPosition) AS ColumnPosition >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO FROM DBC.Columns T1 LEFT JOIN DBC.Indices T2 >> InsUpd.bteq ECHO ON T1.DatabaseName = T2.DatabaseName >> InsUpd.bteq ECHO AND T1.TableName = T2.TableName >> InsUpd.bteq ECHO AND T1.ColumnName = T2.ColumnName >> InsUpd.bteq ECHO WHERE T1.DatabaseName = '%1' >> InsUpd.bteq ECHO AND T1.TableName = '%2' >> InsUpd.bteq ECHO AND IndexType = 'P' ) AS Der) AS DER_1; >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO SEL DER_1.T (TITLE '') >> InsUpd.bteq ECHO FROM >> InsUpd.bteq ECHO (SEL >> InsUpd.bteq ECHO MAX(ColumnPosition) OVER(PARTITION BY DatabaseName, TableName ORDER BY ColumnPosition) AS MaxPos >> InsUpd.bteq ECHO ,CASE WHEN ColumnPosition = 1 AND ColumnPosition = MaxPos THEN 'INSERT ' ^|^| TRIM(DatabaseName) ^|^| '.' ^|^| TRIM(TableName) ^|^| '(' ^|^| TRIM(ColumnName) ^|^|') VALUES' >> InsUpd.bteq ECHO WHEN ColumnPosition = 1 AND ColumnPosition ^< MaxPos THEN 'INSERT ' ^|^| TRIM(DatabaseName) ^|^| '.' ^|^| TRIM(TableName) ^|^| '(' ^|^| TRIM(ColumnName) ^|^| ',' >> InsUpd.bteq ECHO WHEN ColumnPosition ^< MaxPos THEN TRIM(ColumnName) ^|^| ',' >> InsUpd.bteq ECHO WHEN ColumnPosition = MaxPos THEN TRIM(ColumnName) ^|^| ') VALUES ' >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO END AS t >> InsUpd.bteq ECHO ,ColumnPosition >> InsUpd.bteq ECHO FROM (SEL >> InsUpd.bteq ECHO T1.DatabaseName >> InsUpd.bteq ECHO ,T1.TableName >> InsUpd.bteq ECHO ,T1.ColumnName >> InsUpd.bteq ECHO ,ROW_NUMBER() OVER(PARTITION BY T1.DatabaseName, T1.TableName ORDER BY ColumnId) AS ColumnPosition >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO FROM DBC.Columns T1 >> InsUpd.bteq ECHO WHERE T1.DatabaseName = '%1' >> InsUpd.bteq ECHO AND T1.TableName = '%2' >> InsUpd.bteq ECHO ) AS Der) AS DER_1; >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO SEL DER_1.T (TITLE '') >> InsUpd.bteq ECHO FROM >> InsUpd.bteq ECHO (SEL >> InsUpd.bteq ECHO MAX(ColumnPosition) OVER(PARTITION BY DatabaseName, TableName ORDER BY ColumnPosition) AS MaxPos >> InsUpd.bteq ECHO ,CASE WHEN ColumnPosition = 1 AND ColumnPosition = MaxPos THEN '(:' ^|^| TRIM(ColumnName) ^|^|');' >> InsUpd.bteq ECHO WHEN ColumnPosition = 1 AND ColumnPosition ^< MaxPos THEN '(:' ^|^| TRIM(ColumnName) ^|^| ',' >> InsUpd.bteq ECHO WHEN ColumnPosition ^< MaxPos THEN ':' ^|^| TRIM(ColumnName) ^|^| ',' >> InsUpd.bteq ECHO WHEN ColumnPosition = MaxPos THEN ':' ^|^|TRIM(ColumnName) ^|^| '); ' >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO END AS t >> InsUpd.bteq ECHO ,ColumnPosition >> InsUpd.bteq ECHO FROM (SEL >> InsUpd.bteq ECHO T1.DatabaseName >> InsUpd.bteq ECHO ,T1.TableName >> InsUpd.bteq ECHO ,T1.ColumnName >> InsUpd.bteq ECHO ,ROW_NUMBER() OVER(PARTITION BY T1.DatabaseName, T1.TableName ORDER BY ColumnId) AS ColumnPosition >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO FROM DBC.Columns T1 >> InsUpd.bteq ECHO WHERE T1.DatabaseName = '%1' >> InsUpd.bteq ECHO AND T1.TableName = '%2' >> InsUpd.bteq ECHO ) AS Der) AS DER_1; >> InsUpd.bteq ECHO .EXPORT RESET >> InsUpd.bteq ECHO. >> InsUpd.bteq ECHO .LOGOFF >> InsUpd.bteq ECHO .QUIT >> InsUpd.bteq BTEQ < InsUpd.bteq > InsUpd.log ECHO 'Mload script preparation step 2' PAUSE