All Forums Database
venkata_k01 24 posts Joined 07/16
15 Jul 2016
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

M.Saeed Khurram 544 posts Joined 09/12
15 Jul 2016

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

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

Khurram

Khurram

venkata_k01 24 posts Joined 07/16
15 Jul 2016

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

M.Saeed Khurram 544 posts Joined 09/12
15 Jul 2016
CREATE VOLATILE TABLE TABLE_NAME AS
(
SELECT * FROM EXISTING_TABLE
) WITH NO DATA
NO PRIMARY INDEX;

 

Khurram

venkata_k01 24 posts Joined 07/16
15 Jul 2016

thank you very much Khurram.
Its working.

VandeBergB 182 posts Joined 09/06
15 Jul 2016

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>

Some drink from the fountain of knowledge, others just gargle.

venkata_k01 24 posts Joined 07/16
15 Jul 2016
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

venkata_k01 24 posts Joined 07/16
15 Jul 2016
I changed the above create statement as above but its not working.

Small correction to the above post

Johannes Vink 28 posts Joined 08/14
15 Jul 2016

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

M.Saeed Khurram 544 posts Joined 09/12
15 Jul 2016

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

venkata_k01 24 posts Joined 07/16
16 Jul 2016

yes Khurram.Noticed the same while creating the table.
Thank you.

Johannes Vink 28 posts Joined 08/14
18 Jul 2016

I stand corrected ;-)

You must sign in to leave a comment.