Excerpt |
---|
🛠️ DRAFT Step by step technical description of the ETL+ Load Replace process. Work in Progress |
Load Replace Panel UI
Load Replace Settings / Parameters
...
Panel | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
Variables used.
|
Process
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.IF
TargetRowCount
= 0 AND Target T-SQL string begins with “MAX(“ THENRun Load All
End Load Replace process now. ➡️
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>
Save the results of the T-SQL SELECT statement into a variable named
SQLResults.
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
))
WHEREData_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.
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
Calculate
compare-value
variable used in the step below.
IFSQLResults
=NULL
ANDTarget T-SQLNOT = “NULL”
THEN
compare-value
=low-value
! see NOTE below
ELSE
compare-value
=SQLResults
Filter and Extract Source Table into temp/staging table (pseudo query language)
Simple Version. ! pseudo query language
Extract INTO staging-table ! pseudo query language
FROM <source-table>
WHERE <source-table>.source field
>=compare-value
When ETL SQL Statement includes a
JOIN
orUNION
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>
Delete all rows in
target T-SQL
that meet the filter criteriaT-SQL: DELETE FROM target-table WHERE target-table.
source-field
>=compare-value
T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResultsBoth the <source-table> and <target-table> tables must have a field or column as named in the
source-field
parameter.
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
...
Panel | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
Proposed Functions |
❓ 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.
...