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 ...

Get Oracle SQL*Plus Pocket Reference, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.