DRAFT ETL+ Load Replace - Suggested Logic

Load Replace Panel UI

<source field> >= <target T-SQL>

Process

1. TargetRowCount = Obtain the number of rows in the <target-table>.
   Zero means the table has zero rows or the table has yet to be created in the DW.

2. IF TargetRowCount = 0 AND Target T-SQL string begins with “MAX(“ THEN

   1. Run Load All

   2. End Load Replace process now.
      
      

3. Evaluate Target T-SQL into SQLResults

   • T-SQL syntax: SELECT Target T-SQL FROM <target-table>

   • Save the results of the T-SQL SELECT statement into a variable namedSQLResults
     
     

4. low-value variable. Determine the low-value for the date type of <source-table>.source field.
   Pseudo code
   low-value = Low_Value(Data_Type(<source-table>.source field))
   
   

   1. Data_Type – a function that returns the name of the datatype of the source field/row named in the parm.

      • The datatype for all source fields is shown on the Design page.

   2. Low_Value – a function that returns the lowest value for the datatype named in the parm.

      • For instance Low_Value(T-SQL_smallint) = -32,768
        
        

5. IF SQLResults = NULL AND Target T-SQL NOT = “NULL”
   THEN
   compare-value= low value for the data type of <source-table>.source field (left hand box)
   ELSE
   compare-value = SQLResults
   
   

6. Filter and Extract Source Table into temp/staging table (pseudo query language)

   • Extract INTO staging-table ! pseudo query language
     FROM <source-table>
     WHERE <source-table>.source field >= compare-value
     
     

7. Delete all rows in target T-SQL that meet the filter criteria

   • T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResults
     
     

8. Add/Insert records from staging-table into target-table.

   • T-SQL: INSERT FROM staging-table INTO target-table.

Related Pages

• ETL+ Load Replace – update & eventual replacement for Load Replace content in ETL+ Table Load Types

• ETL+ Table Load Types

LOADMAX Option

Concept: Create a new, ETL+ only function called LOADMAX that can be used in place of the MAX function in Target T-SQL.

Replace step # 2 above with

2. IF TargetRowCount = 0 AND Target T-SQL string begins with “LOADMAX(“ THEN

Run Load All
End Load Replace process now.

ELSE