Multitable insert into a table with primary key in Oracle

Question

I'm trying to insert data into different tables using a single table and the tables that I'm inserting into have primary keys.

Can anyone guide me how to fix the error?

INSERT
ALL
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1995' THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1996' THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1997' THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct L_PARTKEY, L_EXTENDEDPRICE, L_SHIPDATE 
FROM LINEITEM;

ERROR at line 1: ORA-00001: unique constraint (TPCHR.YEAR95_PKEY) violated

Here's the table definition

CREATE TABLE YEAR1995
(
    Y95_PARTKEY NUMBER(12) NOT NULL,
    Y95_PRICE   NUMBER(12) NOT NULL,
    CONSTRAINT YEAR95_PKEY PRIMARY KEY (Y95_PARTKEY, Y95_PRICE)  
);

Answer 1

As this article suggests, you may try the following:

exec dbms_errlog.create_error_log ('myInsertErrors');

INSERT
ALL
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1995' THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1996' THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1997' THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct L_PARTKEY, L_EXTENDEDPRICE, L_SHIPDATE 
FROM LINEITEM

LOG ERRORS INTO err$_myInsertErrors REJECT LIMIT UNLIMITED;

This way all your insert exceptions (i.e. duplicate data) will be stored in the err$_myInsertErrors table, and insert statement will be completed

Answer 2

Your l_shipdate isn't unique, so your query gets multiple results within the same year. You can move the year extraction to the query:

INSERT
ALL
WHEN T_YEAR = 1995 THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN T_YEAR = 1996 THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN T_YEAR = 1997 THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct extraxt(YEAR from L_SHIPDATE) AS T_YEAR, L_PARTKEY, L_EXTENDEDPRICE
FROM LINEITEM;

As the calculation is the same for each price you could do that in the query too:

INSERT
ALL
WHEN T_YEAR = 1995 THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)
WHEN T_YEAR = 1996 THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)
WHEN T_YEAR = 1997 THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)


SELECT distinct extraxt(YEAR from L_SHIPDATE) AS T_YEAR,
  L_PARTKEY,
  0.9 * L_EXTENDEDPRICE AS T_PRICE
FROM LINEITEM;

I'd recommend you include the target table's column names in the statement too:

INSERT
ALL
WHEN T_YEAR = 1995 THEN
 INTO YEAR1995 (Y95_PARTKEY, Y95_PRICE)
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)
...

Answer 3

You also really need to understand what a duplicate key is to understand this error:

$ oerr ora 0001
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
//         For Trusted Oracle configured in DBMS MAC mode, you may see
//         this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.
$

See what the Oracle Database Concepts Guide says about unique constraints.