Database Reference
In-Depth Information
Figure 15.8
The New Rows
Were Derived from
a Subquery Join on
Four Tables.
15.3.3
The Multiple-Table INSERT Command
Figure 15.9 describes the syntax for the multiple-table form of the INSERT
command.
Now let's look at an example, once again using the data warehouse
SALES table as a basis. The following query shows a breakdown for the
SALES table by retailer. Next, we use the SALES table to create three sepa-
rate empty tables. Following that we insert rows into all of the three sepa-
rate tables at once. The rows will originate from the SALES table, using a
single multiple-table INSERT command, inserting into the three tables
based on the retailer data in each row. The initial query is shown in Figure
15.10, showing the breakdown of the SALES table based on retailers
(RETAILER_ID).
SELECT (SELECT NAME FROM RETAILER
WHERE RETAILER_ID = S.RETAILER_ID) "Retailer"
, COUNT(S.RETAILER_ID) "Sales"
FROM SALES S GROUP BY S.RETAILER_ID;
Now we can create three empty tables from the SALES table. The
WHERE clause using the ROWNUM < 1 condition is a simple method of
copying the structure of the SALES table without copying any rows. See
Top-N queries in Chapter 5.
