Copying Table structure

UDA

Copying Table structure

Hi,
I have to copy the table structures from one database to another.
I am using below statement

CREATE TABLE ABC.party
as XYZ.party WITH NO DATA ;

But the problem is all tables in database XYZ are partitioned
but while copying the tables to ABC i do not want them to be partitioned.
Above clause takes table defination as available in XYZ and creates.
Anybody help me how to do this?

4 REPLIES

Re: Copying Table structure

Hi,
I am not sure if you can actually do this automatically. You will need to generate the SHOW TABLE results for your XYZ.Table, manually (or automatically using script) edit the Table structure to remove the Partitioning and them run the same on the ABC database.
N/A

Re: Copying Table structure

If you specify the index definition on the create table, it will use the new index definition - so lose the partition information.

So:

CREATE TABLE ABC.party
as XYZ.party
WITH NO DATA
Primary index (col1)
Index (....)

;

You can get the index definition from DBC.Indices.

(V12 or V13 - V2R6 will lose column properties like default/ compress)

HTH

Re: Copying Table structure

Hi,

I do agree with the above solution.

Just create the new primary index on the new table it will not consider the partition from old table and will give the same structure as of old one except partition on the table.

Thanks
Rajeev

Re: Copying Table structure

Thanks everybody.
I have done in same way.