Oracle Multi-row Data Load
-- This syntax allows loading multiple rows into the database 
-- using the DUAL table.  This works fine as long as the
-- sales_id values passed as shown below.

INSERT ALL
 INTO fact_sales_detail(sales_id, customer_name, sales_amt) 
      VALUES (1000, '1IBM2', 25000)
 INTO fact_sales_detail(sales_id, customer_name, sales_amt)
      VALUES (2000, '3MSF4', 18000)
 INTO fact_sales_detail(sales_id, customer_name, sales_amt)
      VALUES (3000, '5TCP6', 13000)
 INTO fact_sales_detail(sales_id, customer_name, sales_amt)
      VALUES (4000, '7FTP8', 13000)
SELECT * FROM DUAL;

-- This fails with unique constraint violation ORA-00001 if
-- sequence.NEXTVAL is used in the load. The table and 
-- sequence are created as shown below.

CREATE TABLE edw_sales_detail
(sales_tran_id      NUMBER(16) NOT NULL PRIMARY KEY,
 loc_id             NUMBER(10) NOT NULL,
 cust_id            NUMBER(10) NOT NULL,
 invoice_num        VARCHAR2(25) NOT NULL,
 invoice_date       DATE NOT NULL,
 sales_amt          NUMBER(12,2) NOT NULL,
 tax_amt            NUMBER(12,2),
 freight_amt        NUMBER(12,2),
 discount_amt       NUMBER(12,2),
 created_date       DATE DEFAULT SYSDATE NOT NULL,
 trans_year         AS (TO_NUMBER(TO_CHAR(invoice_date,'yyyy'))),
 trans_month        AS (TO_NUMBER(TO_CHAR(invoice_date,'yyyymm')))
);

CREATE SEQUENCE sales_tran_id_seq 
START WITH 1 INCREMENT BY 1;

-- The code uses the same syntax to load multiple rows into the
-- database table and the primary key value is populated by 
-- "sales_tran_id_seq.NEXTVAL".

INSERT ALL
 INTO edw_sales_detail(sales_tran_id, loc_id, cust_id,
      invoice_num, invoice_date, sales_amt, tax_amt, freight_amt) 
      VALUES (sales_tran_id_seq.NEXTVAL, 1, 10,
              '1001', SYSDATE, 1000.00, 50.00, 15.00)
 INTO edw_sales_detail(sales_tran_id, loc_id, cust_id,
      invoice_num, invoice_date, sales_amt, tax_amt, freight_amt) 
      VALUES (sales_tran_id_seq.NEXTVAL, 2, 11,
             '1002', SYSDATE, 2000.00, 75.00, 25.00)
SELECT * FROM DUAL;

-- The above syntax fails with a unique constraint violation
-- error (ORA-00001).

Load failure with Unique Constraint violation (ORA-00001)

-- The syntax will work if the primary key value is loaded using a 
-- auto numbering before insert trigger
-- and te key column excluded in the "insert table(column_list..)".


Oracle 10g Syntax - auto numbering before insert trigger

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 27th, 2014

  198