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 SQL stored procedures or having alike procedures or multi-statement SQL load steps with Azure SQL Database and Azure Synapse SQL, there is a known issue and a remedy to include SET NOCOUNT ON; as the first statement in the procedure or in the sequence of multiple of SQL statements. Without it the execution of the statements can fail silently ie. without an error shown. There is a thread on Stack Overflow on this issue.

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;