Importing data using SQL SELECT statement

The SQL Import action allows you to use a SELECT SQL statement to extract data from a source system.

• Source and target systems can be on different or the same connection
• The SQL is executed on the source system, which can reduce the amount of data that otherwise would be transferred if the source data was transferred to the target system

Data can be extracted from the following sources:

• Currently supported:
  • Access
  • ODBC
  • SQL Server
  • SQLite
• No longer supported. You can continue to create and use connections to these databases:
  • Oracle
  • SAP Hana
  • Teradata

On the Configuration tab:

1. Enter the SQL statement.

2. Validate the statement.

3. Preview the extracted data.

The most common application is to extract data from a source system using a SQL query, either across connections or using the same connection.

Import data with SQL SELECT statement

1. Drag the SQL Import icon onto the design surface.
2. On the Connections tab , enter a Name and a Description to identify this action, then specify the input and output database options:

   Input -

   • For Database Connection, select the database from which the data is being imported, or choose New Database Connection to establish a new connection, and then choose the database with the data being imported.

   Output -

   • For Database Connection, specify the database to which the data is being imported, then enter a new Table Name for the data.
3. On the Configuration tab :
   • Enter the SQL statement to be used to extract the data.
   • Click Capture Schema to validate the statement and save the schema.
   • Click Preview SQL to examine the SQL statement to be called.
4. If the Performance tab is enabled in User Preferences, you can optionally change certain execution settings specific to this action. Refer to Change general preferences for additional information.