Wednesday, May 14, 2008

Query vs. CREATE TABLE AS SELECT

Half the time, I want to know some answer that can be determined with a query. The other half of the time, I want to start from that answer and use it as an intermediate step towards some larger solution. (That's not even considering the third half... :-)

So, I have a bunch of .sql files that do SELECT statements. Sometimes, I end up editing those .sql files into new ones that do CREATE TABLE AS SELECT... using the same query. I don't like the proliferation of .sql files that are slight variations of each other. That's the aspect of SQL coding that I would most like to see be object-oriented, the many SQL scripts that differ from each other only in terms of connection string, table name, or other minor detail.

Now, I know the WITH clause is there for pulling out a query to reuse its results as part of a bigger query. But sometimes the natural-feeling technique is to do CREATE TABLE AS SELECT colname... and then several other queries with clauses WHERE colname IN SELECT * FROM newly_created_table.

I had a sense there must be some shortcut to turning the original query into a CTAS construct. And there is! Remember how SQL*Plus will happily substitute the contents of a script anywhere you start a line with an '@' sign? Well, turns out you can use this technique to substitute a script as the last half of a SQL statement:

- Create a .sql file that has a single SELECT statement.
- Issue a CTAS statement like so:

create table t1 as
@script_with_select_statement

The trick is to make sure the SQL script has no blank lines, as they will cause SQL*Plus to stop processing the command prematurely. '--' comments are OK, so you can comment and format the script using -- lines where you would normally use blank lines.

CTAS is a bit of a blunt instrument, especially if the same situation comes up again and again. I may adapt this technique to use with CREATE GLOBAL TEMPORARY TABLE t1... and INSERT INTO t1 SELECT... constructs.

3 comments:

Chris said...

Hi!

Here's another trick that does the job: use edit commands on the sql*plus buffer. I just wrote a script "ctas.sql". It has one input parameter, the new table name. When you run it, it prepends "create table [new table name] as" to your last sql statement and executes this new statement. Here's ctas.sql:

0 create table &1 as
r

You can use it like this:

SQL> select *
2 from dual;

DUM
---
X

1 Zeile wurde ausgewählt.

SQL> @ctas my_copy_of_dual
1 create table &1 as
2 select *
3* from dual
alt 1: create table &1 as
neu 1: create table my_copy_of_dual as

Tabelle wurde erstellt.

SQL> select * from my_copy_of_dual;

DUM
---
X

1 Zeile wurde ausgewählt.

SQL>

Nigel said...

For connection details, table names and other (small) things don't forget the power of SQL*Plus substitution variables (not to be confused with bind variables).

select * from &&table_name

This syntax is also supported by TOAD, which pops up a dialogue to get the values of all variables in a script (are you listening, SQL Developer team?).

You can combine the substitution variable with @ or @@:

@@&&script

@@ is used to get the script from the same directory as the calling script, by the way - saves you having to hard code directory names, and there's no need to pollute your sql directory with spool files.

William said...

Nice tip. I was thinking there was a better way to generate explain plans from a saved .sql file. I'll use this same technique for that. It never occurred to me to try this.

> explain plan for
2 @something.sql

Explained.

Thanks for the post.