All Forums UDA
RB185048 33 posts Joined 09/08
19 Nov 2009
Copying Table structure

Hi,I have to copy the table structures from one database to another.I am using below statementCREATE TABLE ABC.partyas XYZ.party WITH NO DATA ;But the problem is all tables in database XYZ are partitionedbut 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?

20 Nov 2009

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.

Jimm 298 posts Joined 09/07
20 Nov 2009

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.partyas 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

Rajeev T 7 posts Joined 09/09
20 Nov 2009

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

RB185048 33 posts Joined 09/08
22 Nov 2009

Thanks everybody.I have done in same way.

You must sign in to leave a comment.