ETL+ Load Replace Logic

Load Replace Panel UI

Load Replace Settings / Parameters

Open

1 = source field, 2 = 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. Create a T-SQL query from the T-SQL expression in Target T-SQL that results in a single value resolved from Target T-SQL.
   (In short, evaluate the Target T-SQL parameter and resolve into a single value for use in latter steps)
   Depending on the syntax of Target T-SQL the T-SQL query created is either:

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

   • SELECT <Target T-SQL>

4. Save the results of the T-SQL SELECT statement into a variable named SQLResults.
   
   

5. low-value variable used in step #6 below.
   Determine the low-value for the date type of <source-table>.source field. For instance, the low-value of the smallint data type is = -32,768.
   
   PSEUDO CODE
   low-value = Low_Value( Data_Type( <source-table>.source field ))
   WHERE

   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
        
        

6. Calculate compare-value variable used in the step below.
   IF SQLResults = NULL AND Target T-SQL NOT = “NULL”
   THEN
   compare-value= low-value ! see NOTE below
   ELSE
   compare-value = SQLResults
   
   

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

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

   2. When ETL SQL Statement includes a JOIN or UNION clause. ! pseudo query language
      Extract INTO staging-table ! pseudo query language
      FROM <source-table>
      WHERE <source-table>.source field >= compare-value
      <equivalent to JOIN or UNION clause>
      
      

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

   • T-SQL: DELETE FROM target-table WHERE target-table.source-field >= compare-value
     T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResults

     • Both the <source-table> and <target-table> tables must have a field or column as named in the source-field parameter.
       
       

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

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

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

Related Pages

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

• ETL+ Table Load Types

• Data Access Layer & Technology

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