Skip to main content

SQL Query

Use the SQL query feature when you need to retrieve data using more complex logic than selecting fields from a single table. SQL queries can include joins, grouping, summarisation, filtering, subqueries, and any other syntax supported by your database provider. This allows you to prepare and transform data in the database before it is loaded into DataSync.

When to use SQL Query​

  • Joining data: Combine data from multiple tables into a single dataset before loading.
  • Aggregating values: Summarise data using functions like SUM, COUNT, or AVG to reduce volume or prepare metrics.
  • Applying advanced filters: Retrieve only specific rows by filtering on conditions not easily represented.

Add a SQL Query​

  1. From the welcome screen or left navigation pane, select Extractions.
  2. Select an extraction from the list.
  3. Select the link under the Tables column.
  4. Click New, and then choose Add SQL Query.
  5. Enter a name for the destination table.
  6. (Optional) Enter a description.
  7. In the SQL Expression box, enter your SQL statement.
  8. Click Save.

    To edit an SQL statement, hover over the query in the Tables list, select the pencil icon, and update the statement.

SQL statement​

Use the SQL expression box to create a query that extracts the precise data you need. Your statement can include joins, filters, aggregations, subqueries, and any other SQL features supported by your database provider. You can also use variables to parameterise conditions, making the query reusable without hard‑coding values.

important

Include the schema name in your SQL statement if your database requires it.

Example with a variable​

SELECT *
FROM Sales.Orders 
WHERE YEAR(OrderDate) = @YEAR

Example with join and filter​

SELECT *
FROM SEED.SINVOICE
LEFT JOIN SEED.SINVOICED 
    ON SINVOICE_0 = SINVOICED.NUM_0
WHERE YEAR(INVDAT_0) >= 2019

Extraction Variables​

When building a SQL Query, you may need to reference the schema name to access specific tables. Instead of hardcoding these schema names, you can use the system variables ##SourceSchema and ##DestinationSchema. These variables automatically resolve to the Source and Destination schemas defined in the Extraction configuration popup.

Example:

select i.* 
from ##SourceSchema.TableX i
left join ##SourceSchema.TableY c 
    on i.FieldX = c.FieldY