Custom SQL in Database Destinations

This article shows how to use custom SQL statements in the staging steps of database destinations. Custom SQL commands can be used to truncate or delete existing data, perform custom transformations, add post-processing logic, and more.

Create a Custom SQL Statement

To create custom SQL statements in database destinations:

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 and click [ Edit]. The service settings open.
3. Navigate to Destination Settings > Processing.
4. Select the Custom from one of the following dropdowns:
   • Preparation
   • Row Processing
   • Finalization
5. Select a staging command from the Template dropdown, e.g., Drop & Create.
6. Click [Generate Statement]. Xtract Universal.iQ inserts the statement in the Custom Preparation SQL input field.
7. Edit the statement to suit your needs.
8. Click [Save] to save the changes.

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

Use Script Expressions

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

#{Extraction.ExtractionName}#

#{Extraction.TableName }#

#{Extraction.RowsCount }#

#{Extraction.RunState}#

#{(int)Extraction.RunState}#

#{Extraction.Timestamp}#

Example SQL Statements

Create a Status Overview

The depicted example creates a table "ExtractionStatistics" that provides an overview and status of the executed Xtract Universal.iQ services. To create the "ExtractionStatistics" table, create an SQL table according to the following example:

1
2
3
4
5
6
7

CREATE TABLE [dbo].[ExtractionStatistics](
    [TableName] [nchar](50) NULL,
    [RowsCount] [int] NULL,
    [Timestamp] [nchar](50) NULL,
    [RunState] [nchar](50) NULL
) ON [PRIMARY]
GO

The ExtractionStatistics table is filled in the Finalization process step, using the following SQL statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO [ExtractionStatistics]
(
     [TableName], 
     [RowsCount], 
     [Timestamp],
     [RunState]
)
VALUES
(
     '#{Extraction.TableName}#', 
     '#{Extraction.RowsCount}#', 
     '#{Extraction.Timestamp}#',
     '#{Extraction.RunState}#'
);

Add a Timestamp Column

In the depicted example, the SAP standard table KNA1 is extended by a column with the current timestamp of type DATETIME. The new column is filled dynamically using a .NET-based function. The data types that can be used in the SQL statement depend on the database version.

To insert a dynamic column with an extraction timestamp into a table:

1. Open the Services menu.
2. Select a service that uses a database destination and click [ Edit]. The service settings open.
3. Navigate to Destination Settings > Processing.
4. Select Custom from the Preparation dropdown.
5. Select Drop & Create from the Template dropdown.
6. Click [Generate Statement] to use the template of the Drop & Create staging command. Xtract Universal.iQ inserts the statement in the Custom Preparation SQL input field.

7. Add the following line in the generated statement:

   Add New Column

   [Extraction_Date] NATIONAL CHARACTER VARYING(23)
   

8. Select Insert from the Row Processing dropdown.
   At this point, only NULL values are written to the newly created Extraction_Date column.

9. Select Custom from the Finalization dropdown to fill the NULL values with a custom SQL statement.

10. Paste the following SQL statement into the Custom Finalization SQL input field:

    Write Timestamp to Column

    UPDATE [dbo].[KNA1] 
    SET [Extraction_Date] = '#{Extraction.Timestamp}#' 
    WHERE [Extraction_Date] IS NULL;
    

    The NULL values are filled with the current date of the extraction and written to the Microsoft SQL Server target table.
    

11. Click [Save] to save the changes.

12. Run the service to test the changes.

The column Extraction_Date is added to the database table KNA1.

Related Links

• Merge Data in Database Destinations
• Microsoft SQL Server Destination
• Oracle Database Destination
• PostgreSQL Destination