OVERRIDE

OVERRIDE load steps can be used to specifically override automatically generated loads.

See also:

• CONFIG_LOAD_TEMPLATES

• Variables

Usage

OVERRIDE: sql_code

Notes

An OVERRIDE step can be used alone or together with other load steps.

OVERRIDE steps can be saved as part of a load template.

OVERRIDE steps can use variables inside the SQL code.

Known Issue

When calling stored procedures and alike with Azure SQL Database and Azure Synapse SQL there is a known issue to include SET NOCOUNT; as the first statement in the sequence. Without it the execution of the statements can fail silently. A thread on Stack Overflow on it.

Examples

SQL clause to join multiple tables and insert result into a publish table

In this example an automatically generated load is overridden with an INSERT INTO clause joining multiple tables. Override SQLs can be used in a variety of use cases, for example, implementing complex business logic rules.

OVERRIDE:

INSERT INTO pub.f_trip (
...
)
WITH TAXI_DATA AS (
SELECT
..
FROM DV.S_TRIP_GREEN_C
UNION ALL
SELECT
...
FROM DV.S_TRIP_YELLOW_C
)
SELECT
...
FROM DV.H_TRIP H1
	JOIN TAXI_DATA TD ON H1.DV_ID = TD.DV_ID
	JOIN DV.L_TRIP_TAXIZONE_RATECODE L1 ON H1.DV_ID = L1.DV_ID_TRIP
WHERE 1 = 1;

Executed SQL instead of automatically generated load:

INSERT INTO pub.f_trip (
...
)
WITH TAXI_DATA AS (
SELECT
..
FROM DV.S_TRIP_GREEN_C
UNION ALL
SELECT
...
FROM DV.S_TRIP_YELLOW_C
)
SELECT
...
FROM DV.H_TRIP H1
	JOIN TAXI_DATA TD ON H1.DV_ID = TD.DV_ID
	JOIN DV.L_TRIP_TAXIZONE_RATECODE L1 ON H1.DV_ID = L1.DV_ID_TRIP
WHERE 1 = 1;