Table of Contents

Search

  1. Preface
  2. Analyst Service
  3. Catalog Service
  4. Content Management Service
  5. Data Integration Service
  6. Data Integration Service Architecture
  7. Data Integration Service Management
  8. Data Integration Service Grid
  9. Data Integration Service REST API
  10. Data Integration Service Applications
  11. Enterprise Data Preparation Service
  12. Interactive Data Preparation Service
  13. Informatica Cluster Service
  14. Mass Ingestion Service
  15. Metadata Access Service
  16. Metadata Manager Service
  17. Model Repository Service
  18. PowerCenter Integration Service
  19. PowerCenter Integration Service Architecture
  20. High Availability for the PowerCenter Integration Service
  21. PowerCenter Repository Service
  22. PowerCenter Repository Management
  23. PowerExchange Listener Service
  24. PowerExchange Logger Service
  25. SAP BW Service
  26. Search Service
  27. System Services
  28. Test Data Manager Service
  29. Test Data Warehouse Service
  30. Web Services Hub
  31. Application Service Upgrade
  32. Appendix A: Application Service Databases
  33. Appendix B: Connecting to Databases from Windows
  34. Appendix C: Connecting to Databases from UNIX or Linux
  35. Appendix D: Updating the DynamicSections Parameter of a DB2 Database

Application Service Guide

Application Service Guide

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.

0 COMMENTS

We’d like to hear from you!