SQL Execution

There are two step types available for executing SQL statements.

SQL_STATEMENT takes a single SQL INSERT, UPDATE or DELETE sql statement. This is a a straight-forward step type for doing updates to the database. It can access and update Job parameters, but is otherwise run in isolation.

Note that Data List and Data Sheet tables can be referenced by their Entity Names proceeded by @ to avoid having to reference the internal generated table names.

-- A simple update statement
UPDATE my_table SET last_updated = CURRENT_TIMESTAMP

-- An insert statement that references a Job parameter
INSERT INTO my_table (a) SELECT member_id FROM ng_member_tb WHERE category = @MyCategory

-- An insert table into a Data Sheet, using the Sheet name instead of the table name
INSERT INTO @MyDataSheet (a) SELECT COUNT(*) FROM ng_activity;

-- An SQL statement which updates a Job parameter
SELECT @MyJobParameter := MAX(created) FROM ng_member_tb;

-- Two SQL statements executed as one
INSERT INTO ...; UPDATE ...

The number of rows affected by the (last) statement will be read and stored in the Job execution log.

SQL_SCRIPT takes full advantage of the database engine's scripting capabilities. This step type can be used for advanced aggregation and migration processes that requires full database programming capabilities, such as loops and conditional execution.

The SQL script must start with BEGIN and end with END;. It gets compiled into the database as a stored procedure which is then called by the Job step. It is therefore executed as a single atomic unit, which gets committed or rolled back at the end.

As with SQL_STATEMENT it's possible to read and set the job parameters and to reference Data List and Data Sheet by their Entity name.

SQL_SCRIPTs are far more powerful than SQL_STATEMENTs, but can be slower to start (due to compilation), and can be harder to debug and monitor. SQL_SCRIPTS also run a greater risk of causing deadlocks or performance issues if written incorrectly.

It is possible to pass back the number of rows processed, for storing in the Job execution log. This is done by setting the special parameter _rows.

BEGIN
SET @_rows := ...
END;

List Manipulation

To manipulate a List it's usually easier to use a LIST_SET step than using free SQL. The LIST_SET step takes a List reference and a SELECT statement. The selected columns automatically get mapped to List columns with the same name.

The step also takes an action field which controls how the List should be affected by the select statement, such as only insert missing rows, only update existing rows, delete matching rows or replace the whole list with the result of the SELECT statement.

The rules for the specified SELECT statement are:

The first column must be called member_id and it must be of the same type as NEON's member identifier and reference an existing Member.
The SELECT statement must not return duplicate rows (more than one row with the same Member Id)
All other columns must match the column names and types of the List to be modified. For Tag Lists, there are no columns besides the member_id. For Counter Lists there must be one extra column called counter etc.
It's not possible to specify Target Lists or SQL lists
If the action DELETE is specified then only the member_id column is valid.

Note that when updating a Counter List then the counter values will be updated (increased/decreased) with the new value by default. If you wish to replace the existing counter values then you must specify the replaceCounters flag.

Note also that there are restrictions around how Subscription Lists can be updated, so some updates may be ignored.