-- 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). -- 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..)".
52848