Inserting Rows into a Target Table :: SAS(R) Data Integration Studio 4.6: User's Guide

Problem

You want to insert rows into a target table that is included in a SAS Data Integration Studio job.

Solution

You can use the Insert Rows transformation to create an SQL query that will insert the rows into the target table.

Perform the following tasks to insert the rows:

Insert Rows is one of the specialized transformations that are provided in the SQL folder in the SAS Data Integration Studio transformation tree.

The SQL folder is shown the following display:

SQL Folder

These specialized transformations enable you to perform basic SQL tasks in SAS Data Integration Studio jobs. You can use the transformations to create tables, insert, merge, and delete rows, update columns, and execute custom SQL code. You can use the transformations in jobs in the same way that Insert Rows is used in the job described in this topic.

Tasks

Create and Populate the Job

Perform the following steps to create and populate a job that includes the Insert Rows transformation:

Create an empty job.
Select and drag an Insert Rows transformation from the SQL folder in the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the source table out of the Inventory tree. Then, drop it before the Insert Rows transformation on the Diagram tab. For example, you could add the flightdelays table, which contains data about delayed airlines flights, as the source table. The flightdelays table is a SQL Server table.

Note: You can also select the table by clicking the Select a table button next to the Table field on the Source tab.
Drag the cursor from the source table to the input port of the Insert Rows transformation. This action connects the sources to the transformation.
You want to have a permanent target table to contain the output for the transformation. Right-click the temporary work table that is attached to the Insert Rows transformation and click Replace in the pop-up menu. Then, use the Table Selector window to select the target table for the job. In this case, you want to insert selected rows into the SQL Server table flightdelays, so select it as the target table.

The completed flow is shown in the following display:

Insert Rows Job Flow

Note: If you need to use explicit pass-through, the source table and the target table must come from the same database management system. When you use implicit pass-through, the source table and the target table can come from different databases. You must use explicit pass-through if you need to sort the table on the Filter and Sort tab.
Open the properties window for the Insert Rows transformation.
Click Options and select Database pass-through.
Set the SQL procedure pass-through option to Yes. This setting enables the pass-through processing supported by the database management system for the source and target tables.

Filter and Sort the Data

Perform the following steps to filter and sort the rows that you want to insert:

Click Insert. Then, click Edit Query to access the Query Builder window.
Click Filter and Sort.
Click New row above the Filter (WHERE) table to add a row to the table. Then, enter your filter conditions.

The filter conditions are shown in the following display:

WHERE Filter Conditions

This target table will contain only those rows that have a destination of LAX and a delay of more than five minutes. Note that the operand ‘LAX’ is enclosed in single quotation marks. SAS Data Integration Studio cannot successfully generate code for a job that includes a database management system table in which the double quotation mark is used in the table name or the column names. The table that serves as the source and target for this job is a SQL Server table.
Click New row above the Sort (ORDER BY) table to add a row to the table. Then, enter your sort conditions.

The sort conditions are shown in the following display:

Sort Conditions

This setting creates an ascending sort based that is on the Delay column.

Note: The sort function is supported only when explicit pass-through processing is enabled and the source and target tables come from Oracle, DB2, and SQL Server database management systems.
Click OK to save the query and return to the Insert tab. You can review the settings and mappings in the query on the tab.

The following display shows the portion of the SQL query that contains the filter and sort conditions:

SQL Filter and Sort Code
Click OK to save the settings in the properties window and return to the job flow.

Run the Job and Review the Results

Perform the following steps to run the job and review the results.

Run the job.
If the job completes without error, right-click the target table icon and click Open.

The View Data window appears, as shown in the following display:

Insert Rows Results