Oracle INSERT tips

Oracle Database Tips by Donald Burleson

The Oracle INSERT statement allows you to insert data into an Oracle table by inserting one or more rows of data.

The basic Oracle INSERT statement syntax is as follows:

insert into <table name> (col1, col2, col3,…) 
                  values (val1, val2, val3,…);

If the values listed are in the same order as the table columns and there is a value (even if it is NULL) for each column, you can leave out the column list as in the example above. The easiest way to determine which columns are in the table is to describe the table in SQL*plus (SQL> desc author).

If you exclude a column from the Oracle INSERT statement, then the column list is mandatory because the database needs to match the values to the columns. Excluded columns will contain NULL unless the table has a default value defined for that column.

ANSI SQL allows for you to perform a SQL insert operation with a sub-select statement:

insert /*+ append */ into
   finance_users
(
   name,
   salary
)
   select name, salary from emp where dept = 'FINANCE';

Also see these tips for tuning slow insert select statements.

SQL insert select statements can also use scalar subqueries for inserting into tables with values based on other tables. Below we use an insert select with a scalar subquery to compute the maximum credit for BILL and insert this value into a max_credit table.

insert into
   max_credit
(
   name,
   max_credit
)
values
(
   'Bill',
   select max(credit) from credit_table where name = 'BILL'
);

��