30 SQL Developer Tips in 30 Days, Day 19: Quick and Clean Table Copies

Quick and clean, what’s that?

Consider the following:

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=2;

I call that ‘quick and dirty,’ and I imagine you have used this technique many, many times.

But, I wish it would have grabbed column comments, indexes, maybe even the partitions. Partitioning a table after the fact is pretty much a no-go.

So what are we to do?

Well, if you have SQL Developer v4.0.3, you can simply right-click on a table and…

Use as a Template

This grabs the existing table definition and throws it into the create table dialog and lets you give it a new name, and make any changes you’d like.

Me comments! (today is also National Type Like a Pirate Day)

Me partitions!

But wait a second, you’ll have index, constraint, and partition collisions!

Nope. If you look at the DDL generated, the developers have already thought of this and throw a 1 onto the end of those names. You can of course change these to your needs in the create table dialog…

If you’re feeling greedy and wanted the data too, then you can use a INSERT AS SELECT or use one of SQL Developer’s many data offload/onload data tools like the Cart or the Grid Export features.