Add partitioning to a table that is not already partitioned?

[Ryan Harris <Ry...@zionsbancorp.com>]

I'm very aware of the "textbook" approach to creating a partitioned table.

I'm searching for an easy/repeatable solution for the following workflow requirements

1) An initial complex source query, with multiple joins from different source tables, field substring extracts, type conversions, etc that creates an output schema that can be inferred from the source query.

2) The table that is ultimately generated from the data in the query needs to be partitioned.

The results of the query can easily be sent to an un-partitioned table using a CREATE TABLE AS SELECT (CTAS) statement..

I attempted to add a partitioning specification to the CTAS statement, but I received the error:
[Error 10068]: CREATE-TABLE-AS-SELECT does not support partitioning in the target table

I don't think it is possible to add partitioning to a table that doesn't already have partitioning defined, but I'd like to be wrong about this (and see documentation on how to do it)

I would even be okay with a CREATE TABLE LIKE statement that would let me create a PARTITIONED table that is LIKE an un-partitioned table....but I couldn't find that documented anywhere either.

The best thing I can currently think of is:
1) create an initial table using CTAS statement limiting the output size using a LIMIT/WHERE clause
2) use the output from DESCRIBE tablename to generate a CREATE TABLE statement using some scripting language
3) Create the partitioned table using the script output
4) INSERT using the initial query with destination set to newly created partitioned table

This works, but is there an easier way?



======================================================================
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL and may contain information that is privileged and exempt from disclosure under applicable law. If you are neither the intended recipient nor responsible for delivering the message to the intended recipient, please note that any dissemination, distribution, copying or the taking of any action in reliance upon the message is strictly prohibited. If you have received this communication in error, please notify the sender immediately.  Thank you.