LOAD

The Load operation allows you to load changes from a previously created change log and indicates that the SQL CDC script is a write pipeline. It must be the first and only load operation in a SQL CDC script when used.

Unless the full name of a change log is specified, the Load operation will use the next available change log. By design, write pipelines keep a high watermark on their last successful execution timestamp. This timestamp is also part of the naming convention of change logs; this allows the write pipeline to filter the list of change logs available for execution from the last successful write.

The Load operation is not supported in a read pipeline (a pipeline that starts with the SELECT operation), but using SELECT after a LOAD command is supported.

Loads the next available change log, or the log name specified, and optionally limits the data stream to new, modified, or deleted records.

LOAD 
    < { INSERTS } { UPDATES } { DELETES } > 
    
    -- the change log name
    FROM { [CONNECTION] } '...'
    
    { PATH '...' } 

    { WITH 
        { KEY_COLUMNS '... {,...' } }
        { CHECK_SIGNATURE }
        { PREVIEW_FILE '...' }
        { INITIAL_ID '...' }
    }
;

UPSERTS	Loads both Inserted and Updated records from the change log
INSERTS	Loads Inserted records from the change log; if UPSERTS is specified, this option is ignored
UPDATES	Loads Updated records from the change log; if UPSERTS is specified, this option is ignored
DELETES	Loads Deleted records from the change log, if any
CONNECTION	The name of the connection to use to read change logs
PATH	The directory, path, bucket or container to use
KEY_COLUMNS	Optional comma-separated columns to use for UI support when generating scripts
CHECK_SIGNATURE	When specified, checks the signature of the change log
PREVIEW_FILE	Loads the specified change log for sample data in preview operations; if omitted, the last available change log will be loaded if one is available
INITIAL_ID	The change log execution id to use when reinitializing a target or when running the target script the first time

Example 1

-- Load the next set of records previously captured in cloud folder using
-- CAPTURE 'mycdc' INSERT UPDATE ON KEYS 'guid' WITH PATH [adls] '/logs'

-- Specify the key columns to use for UI script generation 
LOAD FROM 'mycdc' PATH [adls] '/logs'
    WITH KEY_COLUMNS 'guid';

-- Save into a database
SINK INTO DB [sql2017] 
    TABLE 'myrssfeed' 
    WITH RECREATE;

PUSH ...

Example 2

-- Load the next set of deleted records previously captured in a local folder using
-- CAPTURE 'mycdc' INSERT UPDATE DELETE ON KEYS 'guid' WITH PATH 'c:\tmp\logs'

LOAD DELETES FROM 'mycdc'
    PATH [adls] '/logs'
    ;

-- Save into a database
SINK INTO DB [sql2017] 
    TABLE 'table001_deleted' 
    WITH RECREATE;

PUSH ...

Example 3

-- Load the next inserts previously captured in a cloud folder using
-- CAPTURE 'mycdc' INSERT UPDATE DELETE ON KEYS 'guid' WITH PATH [adls2.0] 'container'

LOAD INSERTS FROM [adls2.0] 'mycdc' PATH '/container' KEY_COLUMNS 'guid';

-- Save into a database
SINK INTO DB [sql2017] 
    TABLE 'table001_inserted' 
    WITH RECREATE;

PUSH ...

Example 4

-- Load a specific change log previously captured in a local folder
-- CAPTURE 'pipeline001' INSERT UPDATE DELETE ON KEYS 'guid' WITH PATH 'c:\tmp\logs'

LOAD UPSERTS FROM 'pipeline001_1750675200000.eds' PATH 'c:\tmp\logs';

-- Save into a database
SINK INTO DB [sql2017] 
    TABLE 'table001' 
    WITH RECREATE;

-- Now execute this SQL operation, per row
-- The id field from the change log will be used
PUSH INTO DB [sql2017]
ON_UPSERT (
  EXEC load_dataXMLbyId '{{id}}'
) 
WITH BATCH 1;

Example 5

-- Load a change log using the internal CLOUD Agent store
-- Sample capture: CAPTURE 'pipeline001' INSERT UPDATE ON KEYS 'guid''

-- This option is only available for CLOUD Agents; not self-hosted Agents
LOAD UPSERTS FROM 'pipeline001' WITH KEY_COLUMNS 'guid';

-- Now execute this SQL operation, per data stream
-- The table will be created if needed, using guid as the key field
PUSH INTO DB [sql2017]
TABLE 'target1' AUTO_MERGE;