Oracle Database - Insert Statement

> Database > Oracle Database

1 - About

Insert into a table or a view and as a view is based on table, insert inserts data into tables.

When the number of values matches the number of column, the column list at the beginning is not required but if the structure of the table changes, it will fail.

All datatype and constraint must be honoured.

3 - Insert Statement

3.1 - Single table

3.1.1 - with Select

INSERT
INTO
NAME_OF_THE_TABLE1
( 
  COLUMN1,
  COLUMN2,
  ...
)
SELECT
  COLUMN1,
  COLUMN2,
  ...
FROM
  NAME_OF_THE_TABLE2

3.1.2 - with Single values

INSERT
INTO
NAME_OF_THE_TABLE1
( 
  COLUMN1,
  COLUMN2,
  ...
)
VALUES
(
  VALUES1,
  VALUES2,
  ...
)

3.2 - Multi-table

See Multitable insert example

INSERT ALL
   WHEN order_total < 1000000 THEN
      INTO small_orders
   WHEN order_total > 1000000 AND order_total < 2000000 THEN
      INTO medium_orders
   WHEN order_total > 2000000 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;
Advertising
db/oracle/insert.txt · Last modified: 2017/09/06 19:28 by gerardnico