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>
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.
- Subquery Factoring in Oracle 11g
- Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring
- Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
- Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
- Factorial in Oracle SQL – using both new Recursive Subquery and classic Connect By approach
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- Using TRUNC in SQL to get the first date in a period
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation
- AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december
- The APEX of Business Value… or: the Business Value of APEX? Cloud takes Oracle APEX to new heights!