Write SQL queries for Celigo RDBMS exports and imports -- SELECT, INSERT, UPDATE, UPSERT, MERGE, delta, once, and bulk operations across Snowflake, Postgres, MySQL, SQL Server, Oracle, BigQuery, and Redshift. Use when editing rdbms.query or troubleshooting SQL errors.

<! TIER:1 Writing SQL for RDBMS Integrations RDBMS exports and imports use SQL queries to read from and write to relational databases. Queries are Handlebars templates the platform evaluates expressions like {{{record.fieldName}}} at runtime, then executes the resulting SQL against the connected database. This skill covers what SQL to write and how to write it . For which adaptor type to use, connection setup, and queryType selection, see the related skills below. Quick Reference Context decision matrix Where Field Handlebars prefix Braces Example Export query rdbms.query record. (parameterized) or none (standalone) {{{ }}} SELECT FROM orders WHERE id = {{{record.id}}} Export delta query rdbms.query platform injected tokens {{ }} or {{{ }}} WHERE updated at '{{lastExportDateTime}}' Export once mark as processed rdbms.once.query record. {{{ }}} UPDATE orders SET exported = true WHERE id = {{{record.id}}} Import per record query rdbms.query[] record. {{{ }}} INSERT INTO users (name) VALUES ('{{{record.name}}}') Import per page query rdbms.query[] batch of records + record. {{{ }}} {{ each batch of records}}...{{{record.field}}}...{{/each}} Import bulk load override rdbms.bulkLoad.overrideMergeQuery staging table ref {{ }} MERGE INTO target USING {{import.rdbms.bulkLoad.preMergeTemporaryTable}} Key syntax rules 1. Always use record. prefix (AFE 2.0). Never bare fieldName or data.fieldName . 2. Prefer triple braces {{{ }}} for values in SQL. Double braces {{ }} auto wrap values in single quotes in RDBMS context this silently corrupts numeric values and breaks SQL syntax. 3. Add your own quotes for strings: '{{{record.name}}}' . Triple braces give you full control. 4. No quotes for numbers: {{{record.quantity}}} . 5. Import query is an array of strings , not a single string: ["INSERT INTO ..."] . 6. Nested fields use dot notation: {{{record.address.city}}} . Related skills [configuring exports RDBMS](../configuring exports/SKILL.md) adaptorType, delta/once mode, export level config [configuring imports RDBMS](../configuring imports/SKILL.md) queryType decision tree, bulkInsert/bulkLoad config [writing handlebars](../writing handlebars/SKILL.md) full Handlebars syntax, helpers, block expressions [configuring connections RDBMS](../configuring connections/SKILL.md) database connection setup Schema reference [rdbms export.yml](references/schemas/rdbms export.yml) export query and once fields [rdbms import.yml](references/schemas/rdbms import.yml) import query, queryType, bulkInsert, bulkLoad fields <! TIER:2 How to Write a SQL Query 1. Discover the schema Query the live database to find tables and columns before writing SQL: For Snowflake, use fully qualified names: database.schema.table . 2. Determine the query pattern Exports what kind of data fetch? Pattern Export type Query template Full fetch type: null SELECT columns FROM table WHERE conditions Delta/incremental type: "delta" SELECT ... WHERE updated at '{{lastExportDateTime}}' Once (mark as processed) type: "once" rdbms.query = SELECT unprocessed; rdbms.once.query = UPDATE to mark processed Imports what kind of write operation? Pattern queryType What to configure INSERT with duplicate check ["per record"] rdbms.query with INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE ["per record"] rdbms.query with UPDATE ... WHERE UPSERT / MERGE ["per record"] or ["bulk load"] per record: rdbms.query with MERGE; bulk load: bulkLoad.primaryKeys Pure INSERT (no checking) ["bulk insert"] rdbms.bulkInsert.tableName and batchSize no SQL needed Bulk load with upsert ["bulk load"] rdbms.bulkLoad.tableName and primaryKeys auto generates MERGE Custom bulk merge ["bulk load"] bulkLoad.overrideMergeQuery: true + custom SQL referencing staging table 3. Write the SQL Follow the database specific [dialect patterns]( dialect patterns) below. Use {{{record.fieldName}}} for runtime values. 4. Test the query Export Query Patterns Standard SELECT Delta export (incremental) Use {{lastExportDateTime}} (platform injected, not from a record). The token resolves to the timestamp of the last successful export run. For databases that need specific timestamp formats: Once export (mark as processed) Two queries work together. The export rdbms.query fetches unprocessed records; rdbms.once.query marks each one after successful export. JOINs Aggregations Import Query Patterns All import queries use {{{record.fieldName}}} to inject values from incoming records. The query field is an array of strings . INSERT (per record) UPDATE (per record) UPSERT database specific MySQL (ON DUPLICATE KEY UPDATE): PostgreSQL (ON CONFLICT): Snowflake (MERGE): SQL Server (MERGE): Multiple statements (per record) When you need to run multiple SQL statements per record, add them as separate array elements: Bulk insert (no SQL needed) For pure INSERTs with no duplicate checking, use bulkInsert the platform generates the SQL: Column mapping comes from mappings[] on the import (Mapper 2.0). Each mapping's generate value must match a column name in the target table. Bulk load with auto generated MERGE For high volume upsert on Snowflake or Azure Synapse, use bulkLoad with primaryKeys : The platform stages data into a temporary table, then auto generates a MERGE using the primary keys. For composite keys: "primaryKeys": "order id,product id" . Bulk load with custom merge When the auto generated MERGE isn't sufficient (conditional updates, ignore existing, multi table ops), override it: The custom SQL goes in the rdbms.query field (yes, even with bulkLoad ). Reference the staging table via {{import.rdbms.bulkLoad.preMergeTemporaryTable}} : per page batch operations per page gives you the entire page of records as batch of records . Use {{ each}} to iterate: Dialect Patterns Snowflake PostgreSQL MySQL / MariaDB SQL Server / Azure Synapse Oracle BigQuery Redshift <! TIER:3 Pre Submit Checklist Export queries [ ] SELECT query is syntactically valid for the target database dialect [ ] Delta uses {{lastExportDateTime}} in the query, NOT a delta object inside rdbms [ ] Once export has both queries rdbms.query for SELECT and rdbms.once.query for UPDATE [ ] Table and column names exist verify with celigo metadata types/fields <connectionId [ ] Snowflake uses fully qualified names database.schema.table unless the connection sets defaults Import queries [ ] query is an array of strings ["INSERT INTO ..."] , not "INSERT INTO ..." [ ] queryType matches the operation ["per record"] for UPDATE/UPSERT, ["bulk insert"] for pure INSERT, ["bulk load"] for high volume [ ] All {{{record.fieldName}}} paths match incoming data invoke the upstream export to verify field names [ ] Strings are quoted, numbers are not '{{{record.name}}}' vs {{{record.id}}} [ ] Uses record. prefix not bare fieldName or data.fieldName [ ] Uses triple braces {{{ }}} double braces auto wrap in quotes, breaking numeric values and SQL syntax [ ] bulkInsert/bulkLoad not set alongside query these are mutually exclusive with the query field (except overrideMergeQuery ) Cross resource [ ] Connection type is RDBMS type on the connection matches one of: mysql, mariadb, postgresql, mssql, azuresynapse, oracle, snowflake, bigquery, redshift [ ] SQL dialect matches the database MERGE syntax differs between Snowflake, SQL Server, Oracle, PostgreSQL Gotchas 1. Double braces auto format in RDBMS. {{record.name}} outputs 'value' (wrapped in quotes). {{{record.name}}} outputs value (raw). Use triple braces and add your own quotes for strings this gives you control and avoids double quoting or broken numeric values. 2. Import query must be an array. "query": "INSERT INTO ..." fails silently or throws a Cast error. Always use ["INSERT INTO ..."] . 3. queryType values are specific. Use ["per record"] , ["bulk insert"] , ["per page"] , ["bulk load"] . Do NOT use ["INSERT"] or ["UPDATE"] as standalone values. 4. Snowflake rejects legacy queryType values on PUT. insert / update may work on POST but fail on PUT. Use per record or bulk insert from the start. 5. Missing record. prefix produces empty values. {{{name}}} resolves to nothing. Always use {{{record.name}}} . 6. Snowflake requires fully qualified table names. database.schema.table unless the connection sets a default schema. Unqualified names fail silently or hit the wrong table. 7. per page has a different Handlebars context. The context is batch of records , not a single record. Use {{ each batch of records}}...{{{record.fieldName}}}...{{/each}} . 8. bulkLoad.overrideMergeQuery references a staging table. Use {{import.rdbms.bulkLoad.preMergeTemporaryTable}} not the target table name. 9. SQL Server MERGE requires a semicolon terminator. Missing ; at the end causes syntax errors. 10. once.query runs per record, not per batch. The {{record.id}} in the once query refers to the current exported record. Don't write batch UPDATE statements here. 11. Don't put delta config inside rdbms . There's no rdbms.delta property. Delta is handled by embedding {{lastExportDateTime}} directly in the SQL query text. 12. NULL handling varies by dialect. Use NVL (Oracle), IFNULL (MySQL), COALESCE (standard/Snowflake/PostgreSQL/SQL Server). Don't assume one works everywhere. Common Errors Error Likely Cause Fix Cast error or Invalid query format query is a string instead of an array Change to ["SQL here"] Empty values in SQL / NULL where data expected Missing record. prefix in Handlebars Use {{{record.fieldName}}} '42' instead of 42 for numeric field Double braces auto quoting Switch to triple braces {{{ }}} Compilation error: Object does not exist (Snowflake) Unqualified table name Use database.schema.table Invalid value for queryType on PUT Legacy insert / update queryType Use per record or bulk insert Merge statement must be terminated by ; (SQL Server) Missing semicolon at end of MERGE Add ; after the final clause Ambiguous column reference JOIN without table alias Prefix columns with table aliases Syntax error near ON DUPLICATE KEY Using MySQL syntax on PostgreSQL/Snowflake Use the correct dialect: ON CONFLICT (Postgres) or MERGE (Snowflake) Column count doesn't match value count Mismatch between INSERT columns and VALUES Verify column list matches the number of {{{record.x}}} values Permission denied for table Connection user lacks INSERT/UPDATE grants Check database permissions for the connection user

writing-sql