Specifying pre- or post-processing SQL statements

If you are using a Select or Extract process, you can optionally include raw SQL statements to run before or after the process.

Before you begin

For important information, see Guidelines for using raw SQL queries.

About this task

You can include SQL procedures as part of the process run, which can be useful for ETL, routine data mart updates, performance tuning, and security. You can specify pre- or post-processing SQL:

  • Pre-processing: Enter raw SQL to be processed before the query runs.
  • Post-processing: Enter raw SQL to be processed after the query runs.

For example, you can use pre- and post-processing SQL statements to:

  • Run stored procedures in the database
  • Create, drop, and re-create tables and indexes
  • Grant or change privileges to other users or groups
  • Organize multistep database commands
  • Run complex database routines without having to use external scripts to connect to the database
Note: For important information, see Guidelines for using raw SQL queries.

Procedure

  1. Begin configuring a Select or Extract process.

    You can select all records or use a query to select specific IDs. For the Select process, you can apply pre or post processing regardless of the query type (standard query or Use Raw SQL for Record Selection).

  2. Click the Advanced button.

    The Advanced Settings window appears.

  3. Double-click in the Pre-Processing area and enter a raw SQL statement to run before the process.
  4. Click in the Database cell, and select the database on which you want to run this statement.

    The Database list shows all available databases (those for which a data source category was configured on the Configuration page in Marketing Platform). If your database does not appear in the list, contact your Campaign system administrator. You must enter a SQL statement before you can select a database.

    The SQL statements are processed in the order in which they appear.

  5. Follow the same procedure to enter any Post-Processing SQL statements to run after the process.
    The SQL statements are processed in the order in which they appear.
    Note: For information about the Use Raw SQL for record selection option in the Advanced Settings dialog, see Creating raw SQL queries.