Choosing Select Columns in Oracle INSERT ALL Statement

oracle, sql

Solution

Yes:

INSERT ALL
INTO tab1
  (col1, col2, col3) values (a, b, c)
INTO tab2
  (col1, col3) values (a, c)
SELECT a, b, c
FROM
( ...
 complex subquery which returns A, B, C
  ...
)

Problem

I am using an `INSERT ALL` statement in Oracle to insert data into two different tables. The data is coming from a rather complex subquery rather than a `VALUES` clause. My issue is that I don't want to insert all the columns returned by the subquery into both tables. As a simple example, suppose I have two tables `tab1` and `tab2` each containing three columns `col1`, `col2` and `col3`. Now suppose my subquery returns a single row with the values `A`, `B` and `C`. So, I want `A`, `B` and `C` inserted into `col1`, `col2` and `col3` of `tab1` respectively, but I want, say, only `A` and `C` to go into `col1` and `col3` of `tab2`. My statement would look something like this: ``` INSERT ALL INTO tab1 (col1, col2, col3) INTO tab2 (col1, ?, col3) FROM ( ... complex subquery which returns A, B, C ... ) ``` Is there a way I can use some sort of a 'filler' or 'garbage column' to allow me to achieve my objective?

Original source