How often do you need to build a new table to test something out?
How often have you typed CREATE TABLE AS SELECT…
If the answer to both of those question is ‘quite a bit Jeff!,’ then continue reading.
In SQLcl, we have built a new command simply called ‘CTAS.’
So let’s try it out.
What tables do I have to work with?
I’m lazy, so instead of typing ‘select table_name from user_tables’ I just types ‘tables’ and execute that.
It’s a burned-in sample of one of our ALIASes. With the ALIAS command you can save your own statements for re-use. Anyways, I want a copy of the EMPLOYEES table.
CTAS existing_table new_table
So nothing has been executed, but what have we done really?
Well, we generated the DDL for the EMPLOYEES table, substituted the new table name PEEPS in instead, added the ‘as select * from EMPLOYEES’ to it, and threw THAT into the command buffer.
So, when I hit edit, we’ll launch the default editor (I have mine set to the inline editor in SQLcl, or you could use Notepad or vi or whatever)
I now have my new table. The more complicated your base table is, the more typing this will save you. Imagine partitions…