How to CREATE TABLE(copy) without any index and parititions present on source table

Database
Enthusiast

How to CREATE TABLE(copy) without any index and parititions present on source table

Hi,

I am working on one automation process and in the middle of the process  i need to create a temporary table with same column structure as source table but any index or partitions declared on the source table should not be present on temporary table.

I tried with the below statement

CREATE TABLE db.temptable as db.sourcetable with no data and stats;

But all the unique index's and Partitions declared on sourcetable also present on temptable.

Is there any way to create temptable without any index's and partitions declated on source table ?

Thanks,

Hanu

11 REPLIES
Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

Hi,

You can create a NOPI Global temporary or volatile table in your script.

Create Volatile Table TableName (......) 
NO PRIMARY INDEX

Khurram

Khurram
Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

Hi Khurram,

I am trying to create temporary table without using DDL of the source table.In the above statement, I think i need to mention complete DDL.

Thanks,

Hanu

Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

CREATE VOLATILE TABLE TABLE_NAME AS
(
SELECT * FROM EXISTING_TABLE
) WITH NO DATA
NO PRIMARY INDEX;
Khurram
Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

thank you very much Khurram.

Its working.

Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

you'll need to add the on commit preserve rows if you want your data to stick around, and depending upon the number of rows in the volatile table, you may have introduced some significant spool issues by declaring it as a NOPI table.  You should at least maintain the PI from the source table, or define a new PI>

Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

CREATE TABLE UDWETLWORK_D5.MBR_COV_TST_tmp AS
(
SELECT * FROM UDWETLSANDBOX.ADJD_MCE_TST
) WITH NO DATA
WITH PRIMARY INDEX;

Yes i am also feeling the same to maintain PI from the source table.

I changed the above create statement as above but its working.

I need only PI on the target table not any other index's and unique index.

Khurram or Van, Could you please help on that.

Thanks,

Hanu

Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

I changed the above create statement as above but its not working.

Small correction to the above post

Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

CREATE TABLE UDWETLWORK_D5.MBR_COV_TST_tmp AS

(

SELECT * FROM UDWETLSANDBOX.ADJD_MCE_TST

) WITH NO DATA

WITH PRIMARY INDEX (FIELD1, FIELD2);

 

This should work

Enthusiast

Re: How to CREATE TABLE(copy) without any index and parititions present on source table

Hi,

There is no need to write WITH PRIAMRY INDEX, WITH is required for NO PRIMAARY INDEX. Normall Syntax is:

CREATE TABLE UDWETLWORK_D5.MBR_COV_TST_tmp AS
(
SELECT * FROM UDWETLSANDBOX.ADJD_MCE_TST
) WITH NO DATA
PRIMARY INDEX (Colmne_Name);
Khurram