Database Reference
In-Depth Information
sets missing columns
to null values except when a default value is defined for a column. In that
case, Oracle fills the column with the default value. If you omit any non-
nullable columns, which do not have a default value setting, then an error
will result.
When you omit columns, Oracle Database 10
g
15.3.2
Inserting with a Subquery
You can also insert a group of rows all at once using a subquery instead of
a list of values. Each row returned by the subquery becomes a row
inserted into the table. In this example, we create a table and insert rows
using a subquery.
CREATE TABLE TESTMUSICCD(
TITLE
VARCHAR2(32
)
,
ARTIST_NAME
VARCHAR2(32) NOT NULL
,
PRESSED_DATE
DATE
, ARTIST_COUNTRY
VARCHAR2(32));
Now we use an INSERT statement to query the ARTIST and MUS-
ICCD tables and load the resulting rows into the new table.
INSERT INTO TESTMUSICCD
SELECT DISTINCT M.TITLE, A.NAME, M.PRESSED_DATE
, A.COUNTRY
FROM ARTIST A , SONG S, CDTRACK T, MUSICCD M
WHERE A.ARTIST_ID = S.ARTIST_ID
AND S.SONG_ID = T.SONG_ID
AND T.MUSICCD_ID = M.MUSICCD_ID;
This INSERT command creates 13 rows at once. Figure 15.8 shows the
new rows using the following simple query:
SELECT * FROM TESTMUSICCD;
The rows in the table have not yet been saved to the database. We could
save them by executing a COMMIT command. And now that you have
some data in a new table, you can experiment with updates and deletes.
However, first let's examine multiple-table inserts.
