Oracle SQL*Plus Pocket Reference, 3rd Edition

Inserting Data

Use the INSERT statement to add new rows to a table. In Oracle9i Database and higher, you have the ability to perform direct path and multitable inserts. For the examples in this section, I’ve added a column to the COURSE table shown previously in the section on Table Joins (Oracle9i and Higher):

ALTER TABLE COURSE ADD (
   course_hours NUMBER DEFAULT 4);

Inserting One Row

To insert one row into a table, specify the list of columns for which you wish to insert a value and use the VALUES clause to specify values for the columns in your list:

INSERT INTO COURSE (course_name, period, course_hours)
VALUES ('French I', 5, DEFAULT);

The DEFAULT keyword is new in Oracle9i Database and is used in this query to explicitly request the default value for the course_hours column. You can use the NULL keyword, available in all releases of Oracle, to explicitly insert a null into a column.

You can omit the list of columns if you provide a value for each column in your table, and if you provide those values in the same order in which the columns are listed when you DESCRIBE the table:

INSERT INTO COURSE
VALUES ('French I', 5, DEFAULT);

I don’t recommend this shortcut unless you are just typing in a one-off query interactively. It’s safer to specify the column names.

Inserting the Results of a Query

Use the INSERT...SELECT...FROM syntax to insert the results of a query into a table. For example, the following INSERT statement creates a new row in the COURSE table for any currently undefined courses ...