I use subquery factoring a lot, to make my SQL queries easier to write. This basically means that most of my queries these days start with WITH rather than SELECT. (this feature was introduced in the 9i release of the Oracle Database).

Today I was wondering whether I can also use subquery factoring in INSERT statements. You probably are familiar with INSERT statements that go like:

INSERT INTO <tablename>

(column1, columns2,….)

SELECT value1,value2,…

FROM … [rest of the query]

Such statements allow us to quickly and in a focused way load a table with records read from other tables (or PL/SQL Table Functions).

The question I had was: can I use WITH <subquery> as <alias>[, <subquery> as <alias>] SELECT value1,value2 from subquery,…. [rest of the query] inside an INSERT statement.

Best way to find it out – try and do it.

(more…)