Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Common Content for Data Engineering 10.2.2 Service Pack 1
  • All Products

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 Applications
  10. Interactive Data Preparation Service
  11. Enterprise Data Preparation Service
  12. Informatica Cluster Service
  13. Mass Ingestion Service
  14. Metadata Access Service
  15. Metadata Manager Service
  16. Model Repository Service
  17. PowerCenter Integration Service
  18. PowerCenter Integration Service Architecture
  19. High Availability for the PowerCenter Integration Service
  20. PowerCenter Repository Service
  21. PowerCenter Repository Management
  22. PowerExchange Listener Service
  23. PowerExchange Logger Service
  24. SAP BW Service
  25. Search Service
  26. System Services
  27. Test Data Manager Service
  28. Test Data Warehouse Service
  29. Web Services Hub
  30. Application Service Upgrade
  31. Appendix A: Application Service Databases
  32. Appendix B: Connecting to Databases from Windows
  33. Appendix C: Connecting to Databases
  34. Appendix D: Updating the DynamicSections Parameter of a DB2 Database

Application Service Guide

Application Service Guide

Back Next

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!