Oracle SQL: Using subquery factoring in an INSERT statement


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.

I have create a simple table (required for some other demo scenario) with seats in airplane:


I would like to create rows for this table. Let’s assume a plane with 46 rows and 6 seats in each row. Would it be possible to create these SEAT records using this statement:


Copy and paste the statement into SQL*Plus and execute. Low and behold:


The conclusion can be: WITH and subquery factoring can in fact be used in INSERT statement, allowing even greater control over which rows will be generated and/or selected to be inserted.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on community events and conferences such as JavaOne, Oracle Code and Oracle OpenWorld.

Comments are closed.