Table of Contents

Search

  1. Preface
  2. Analyst Service
  3. Content Management Service
  4. Data Integration Service
  5. Data Integration Service Architecture
  6. Data Integration Service Management
  7. Data Integration Service Grid
  8. Data Integration Service Applications
  9. Metadata Manager Service
  10. Model Repository Service
  11. PowerCenter Integration Service
  12. PowerCenter Integration Service Architecture
  13. High Availability for the PowerCenter Integration Service
  14. PowerCenter Repository Service
  15. PowerCenter Repository Management
  16. PowerExchange Listener Service
  17. PowerExchange Logger Service
  18. SAP BW Service
  19. Search Service
  20. System Services
  21. Test Data Manager Service
  22. Web Services Hub
  23. Application Service Upgrade
  24. Application Service Databases
  25. Connecting to Databases from Windows
  26. Connecting to Databases from UNIX
  27. Updating the DynamicSections Parameter of a DB2 Database

Temporary Table Operations

Temporary Table Operations

After you create the SQL data service connection, you can use SQL operations to create, populate, select from, or drop a temporary table. You can issue these commands in a regular or stored SQL statement.
You can perform the following operations:
Create a temporary table.
To create a temporary table on the relational database, use the following syntax:
CREATE TABLE emp (empID INTEGER PRIMARY KEY,eName char(50) NOT NULL,)
You can specify the table name in the SQL data service.
Use
CREATE TABLE
, not
CREATE TEMPORARY TABLE
. The use of
CREATE TEMPORARY TABLE
is not supported.
Create a temporary table from a source table.
You can create a temporary table with or without data from a source table.
The following syntax is supported in Informatica Data Services version 9.5.1:
CREATE TABLE emp.backup as select * from emp
Where
emp
is an existing schema in the SQL data service that you connected to.
The following syntax is supported in Informatica Data Services version 9.6.0 and 9.6.1:
CREATE TABLE emp.backup as select * from emp [ [LIMIT n] ]
Where
emp
is an existing schema in the SQL data service that you connected to.
When you create a temporary table with data, the Data Integration Service populates the table with the data. The
CREATE AS
operator copies columns from a database table into the temporary table.
You cannot maintain foreign key or primary key constraints when you use
CREATE AS
.
You can cancel a request before the Data Integration Service copies all the data.
The Informatica administrator must create a connection, and then configure it in
SQL Properties
as the
Table Storage Connection
, before you create the temporary table.
Insert data into a temporary table.
To insert data into a temporary table, use the
INSERT INTO <temp_table>
statement. You can insert literal data and query data into a temporary table.
The following table shows examples of SQL statements that you can use to insert literal data and query data into a temporary table:
Type
Description
Literal data
Literals describe a user or system-supplied string or value that is not an identifier or keyword. Use strings, numbers, dates, or boolean values when you insert literal data into a temporary table. Use the following statement format to insert literal data into a temporary table:
INSERT INTO <TABLENAME> <OPTIONAL COLUMN LIST> VALUES (<VALUE LIST>), (<VALUE LIST>)
For example,
INSERT INTO temp_dept (dept_id, dept_name, location) VALUES (2, 'Marketing', 'Los Angeles')
.
Query data
You can query an SQL data service and insert data from the query into a temporary table. Use the following statement format to insert query data into a temporary table:
INSERT INTO <TABLENAME> <OPTIONAL COLUMN LIST> <SELECT QUERY>
For example,
INSERT INTO temp_dept(dept_id, dept_name, location) SELECT dept_id, dept_name, location from dept where dept_id = 99
.
You can use a set operator, such as
UNION
, in the SQL statement when you insert query data into a temporary table. Use the following statement format when you use a set operator:
INSERT INTO <TABLENAME> <OPTIONAL COLUMN LIST> (<SELECT QUERY> <SET OPERATOR> <SELECT QUERY>)
For example,
INSERT INTO temp_dept select * from north_america_dept UNION select * from asia_dept
.
Select from a temporary table.
You can query the temporary table with the
SELECT ... from <table>
statement.
Drop a temporary table.
To drop a temporary table from the relational database, use the following syntax:
DROP TABLE <tableName>
If the table is not dropped on the physical database, the SQL data service drops the table the next time the Data Integration Service starts, if the table still exists.