Skip to content

Custom SQL Statements in Database Destinations

This article shows how to use custom SQL statements in database destinations.

The Xtract Universal.iQ database destinations support the use of custom SQL statements in the staging steps of the database.

Create a Custom Statement

To create custom SQL statements in Xtract Universal.iQ:

  1. Open the Services menu.
  2. Select a service that uses a database destination, e.g., Microsoft SQL Server.
  3. Click [ Edit]. The service settings open.
  4. Open the Destination Settings tab.
  5. Select the option Custom SQL from one of the following drop-downs:
    • Preparation
    • Row Processing
    • Finalization
  6. Click [Edit SQL]. The window "Edit SQL" opens.
  7. Enter your custom SQL statement.
  8. Click [OK]. The window closes.
  9. Click [Save].

The custom SQL statement is executed in the selected staging step.

Use Templates

Existing SQL commands can be used as templates to:

  • write user-defined SQL expressions and adapt the data load to your needs.
  • execute stored procedures that exist in the database.

To generate a Custom SQL command from a template:

  1. Open the Services menu.
  2. Select a service that uses a database destination, e.g., Microsoft SQL Server.
  3. Click [ Edit]. The service settings open.
  4. Open the Destination Settings tab.
  5. Select the option Custom SQL from one of the following drop-downs:
    • Preparation
    • Row Processing
    • Finalization
  6. Click [Edit SQL]. The window "Edit SQL" opens.
  7. Select a staging command from the dropdown, e.g., Drop & Create.
  8. Click [Generate Statement]. A new statement is generated.
  9. Edit the statement to suit your needs or click [Copy] to copy the statement to your clipboard.
  10. Click [OK]. The window closes.
  11. Click [Save].

The custom SQL statement is executed in the selected staging step.

Note

The custom SQL code is used for SQL Server destinations. A syntactic adaptation of the code is necessary to use the custom SQL code for other database destinations.

Use Script Expressions

You can use script expressions for Custom SQL commands. The following Xtract Universal.iQ specific custom script expressions are supported:

Input Description 
#{Extraction.ExtractionName}#
 Name of the service. 
#{Extraction.TableName }#
 Name of the database table that data is written to. 
#{Extraction.RowsCount }#
 Number of extracted rows. 
#{Extraction.RunState}#
 Status of the service (Running, FinishedNoErrors, FinishedErrors, Cancelled). 
#{(int)Extraction.RunState}#
 Status of the service as number (2 = Running, 3 = FinishedNoErrors, 4 = FinishedErrors, 6 = Cancelled). 
#{Extraction.Timestamp}#
 Timestamp of the service.
Example: Verify the existence of a table in a database using 'ExistsTable'
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
#{
   iif
   (
      ExistsTable("MAKT"),
      "TRUNCATE TABLE \"MAKT\";",
      "
         CREATE TABLE \"MAKT\"(
            \"MATNR\" VARCHAR(18),
            \"SPRAS\" VARCHAR(2),
            \"MAKTX\" VARCHAR(40));
      "
   )
}#
Last update: June 24, 2026
Written by: Valerie Schipka