About Other SQL Transformations

Overview

The SQL folder in the Transformation tree contains a number of transformations that enable you to add SQL processing to jobs. This chapter is about the SQL transformations other than the Join transformation.

In addition to the Join transformation, the SQL folder contains the following transformations:

Other SQL Transformations
Name	Description
Create Table	Provides a simple SQL interface for creating tables.
Delete	Generates a PROC SQL statement that deletes user-selected rows in a single target table. Supports delete, truncate, or delete with a WHERE clause. Also supports implicit and explicit pass-through.
Execute	Enables you to specify custom SQL code to be executed and provides SQL templates for supported databases.
Extract	Selects multiple sets of rows from a source and writes those rows to a target. Typically used to create one subset from a source. Can also be used to create columns in a target that are derived from columns in a source. For more information, see Extracting Data from a Source Table.
Insert Rows	Provides a simple SQL interface for inserting rows into a target table. For more information, see Inserting Rows into a Target Table.
Merge	Inserts new rows and updates existing rows using the SQL Merge DML command. The command was officially introduced in the SQL:2008 standard.
Set Operators	Enables you to use set operators to combine the results of table-based queries. For more information, see Using the SQL Set Operators Transformation.
Update	Updates user-selected columns in a single target table. The target columns can be updated by case, constant, expression, or subquery. Handles correlated subqueries.

Some functions in the Delete, Execute, Insert Rows, Merge, and Update transformations might work only when the table comes from a database management system that provides an implementation of an SQL command for which a SAS/ACCESS interface is available. One example is sort. You can use SAS tables and tables from database management systems that do not implement the SQL command, but these command-specific functions might not work.

You should enable explicit pass-through processing when you connect a database management system table to the Create Table transformation, Delete transformation, Insert Rows transformation, and Update transformation. For more information, see Enabling Explicit Pass-Through Processing for Other SQL Transformations.

See also the SQL-related usage notes in General Usage Notes. For information about the Join transformation, see Working with SQL Join Transformations.

Query Builder Window

The Query Builder window provides a convenient interface for creating SQL queries within transformations in SAS Data Integration Studio jobs. You can access the Query Builder or its components in the following transformations:

Insert Rows — displays the Query Builder window when you click Edit Query on the Insert tab.
Create Table — incorporates the tabs from the Query Builder window into its properties window.
Additional SQL transformations that support subqueries include Delete, Update, and Merge. Note that the subquery version of the Query Builder window includes a Name (ALIAS) field. This field enables you to specify an alias for the subquery when it is used in a query.

The Query Builder window contains the following tabs:

Tabs	Description
Source	Identifies the tables used in a query. When multiple tables are selected, you can specify the join type and any applicable join conditions. Finally, you can create a subquery that you can use as the source of a query. Note: In the Subquery window, tables from all database management systems are handled in the same way. The interface in the window does not change to reflect the differences in how the various database management systems implement the SQL MERGE command. Therefore, it is possible to generate invalid SQL Merge code by using features that are not supported by a specific database management system. When you encounter SQL Merge errors, review the log for the SAS Data Integration Studio job. Also, see the documentation for the database management system for information about its implementation of the SQL MERGE command.
Result	Maps source tables to a target table. The tab uses the standard SAS Data Integration Studio mapping component.
Filter and Sort	Filters and sorts query results.
Group	Groups query results. You can also use the tab to filter the groups with a HAVING clause.
Code	Manages the code that is generated.