|
OraDev.com
|
Oracle INSERT statement
The INSERT statement in Oracle is used to add rows to a table, the base table of a view,
a partition of a partitioned table or a subpartition of a composite-partitioned table,
or an object table or the base table of an object view.
For you to insert rows into a table, the table must be in your own schema or you must have the INSERT object privilege on the table.
For you to insert rows into the base table of a view, the owner of the schema containing the view must have the INSERT object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the INSERT object privilege on the view.
If you have the INSERT ANY TABLE system privilege, then you can also insert rows into any table or the base table of any view.
|
Basic insert of a few columns of a table or view
|
INSERT INTO (column1, column2,column3) values (value1,value2,value3);
INSERT INTO (column1, column2,...,columnn) values (value1,value2,...,valuen);
| |
Insert into a table/view specifying all columns
|
If you insert all columns in the same order as they are stored in the table,
you can ommit the columnnames in the insert statement like this:
INSERT INTO VALUES (val1,val2,val3,val4);
| |
Insert data from a select statement.
|
INSERT INTO select * from ;
INSERT INTO select col1,col2,col3 from where col1='val1';
|
INSERT when
|
The insert when command can do a conditional insert.
Syntax:
INSERT
WHEN ([Condition]) THEN
INTO [TableName] ([ColumnName])
VALUES ([Values])
ELSE
INTO [TableName] ([ColumnName])
VALUES ([Values])
SELECT [ColumnName] FROM [TableName];
sample code:
INSERT
WHEN mod(deptno,2)=0 THEN
INTO even_employees (empno, ename)
VALUES (empno, ename)
WHEN mod(deptno,2)=1 THEN
INTO uneven_employees (empno, ename)
VALUES (empno, ename)
ELSE
INTO unknow_employees (empno, ename)
VALUES (empno, ename)
SELECT empno, ename, deptno from emp;
There are 2 modes for conditional insert: ALL and FIRST
If you specify ALL, the default value, then the database evaluates each WHEN clause regardless of the results
of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true,
the database executes the corresponding INTO clause list.
If you specify FIRST, then the database evaluates each WHEN clause in the order in which it appears in the statement.
For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent
WHEN clauses for the given row.
|
Multi table insert
|
You can also use the INSERT ALL statement without a condition.
This provides you with an multiple inserts
Example:
INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;
| |
create table with insert
|
CREATE TABLE AS select * from ;
Example: create table emp2 as select * from emp;
|
|
|
|
