0

I use SQL Loader to bulk load data into the staging table (no problems there). But the table that I want to insert data into is not taking any tuples as it is complaining about unique constraint through a bash script.

What are some fixes for inserting data into the main table?

Main table schema

CREATE TABLE "SYSTEM"."DATA_SM_METRIC" 
(
    UNIQUE_ID   VARCHAR2(250 CHAR) NOT NULL,
    METRIC_REVIEW_CRITERION_DESC    VARCHAR2(250 CHAR) NOT NULL,
    METRICS_REVIEW_CRITERION_VALUE  VARCHAR2(250 CHAR) NOT NULL, 
    ENTITY_TYPE VARCHAR2(250 CHAR) NOT NULL,
    REVIEWCRITERIATYPE  VARCHAR2(25 CHAR) NOT NULL,
    CREATETIME DATE Default sysdate,                       
    CONSTRAINT DATA_SM_METRIC_PK PRIMARY KEY (UNIQUE_ID,METRIC_REVIEW_CRITERION_DESC,CREATETIME)   
) 
;

Staging Table Schema

CREATE TABLE "SYSTEM"."DATA_SM_METRIC_STG" 
(
    UNIQUE_ID   VARCHAR2(250 CHAR) NOT NULL,
    METRIC_REVIEW_CRITERION_DESC    VARCHAR2(250 CHAR) NOT NULL,
    METRICS_REVIEW_CRITERION_VALUE  VARCHAR2(250 CHAR) NOT NULL, 
    ENTITY_TYPE VARCHAR2(250 CHAR) NOT NULL,
    REVIEWCRITERIATYPE  VARCHAR2(25 CHAR) NOT NULL,
    CREATETIME DATE Default sysdate,
    CONSTRAINT DATA_SM_METRIC_STG_PK PRIMARY KEY (UNIQUE_ID,METRIC_REVIEW_CRITERION_DESC,CREATETIME)                                        
)
;

Shell Script as follows:

#!/bin/ksh

DATAFILE=$1

SQLPATH=/u01/app/oracle/product/12.2/db_1/bin

SQLPLUS=$SQLPATH/sqlplus

SQLLOADER=$SQLPATH/sqlldr

echo "Start loading file into staging table ->sqlldr $DATAFILE"

$SQLLOADER userid=system/oracle@127.0.0.1:1521/orcl12c 
control=./$DATAFILE.ctl data=./$DATAFILE log=./$DATAFILE.log  
bad=./$DATAFILE.bad skip=1 errors=100
echo $?

$SQLPLUS -S system/oracle@//127.0.0.1:1521/orcl12c <<EOF | sed 1,3d  
select count(*) from data_sm_metric_stg;

INSERT INTO data_sm_metric(unique_id,metric_review_criterion_desc,metrics_review_criterion_value,entity_type,reviewcriteriatype)



SELECT unique_id,metric_review_criterion_desc,metrics_review_criterion_value,entity_type,reviewcriteriatype FROM data_sm_metric_stg;

select unique_id from data_sm_metric;
exit
EOF

ERROR:

INSERT INTO data_sm_metric(unique_id,metric_review_criterion_desc,metrics_review_criterion_value,entity_type,reviewcriteriatype)


 *
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.DATA_SM_METRIC_PK) violated
Akash Sharma
  • 93
  • 1
  • 12
  • add in you are select where not exists in this way youll not have unique constraint error – Moudiz Jul 03 '18 at 15:22
  • I don't think the shell script is the problem - it's your data. You're trying to load a row from the staging table with the same key (UNIQUE_ID, METRIC_REVIEW_CRITERION_DESC, CREATETIME) as an existing row. What do you want to do in this scenario? – Christian Palmer Jul 03 '18 at 15:28
  • @ChristianPalmer I am wanting to just transfer the same data from whatever was inserted into staging to main table. I was under the impression that createtime that is created by default would it make every record unique as a timestamp data value. – Akash Sharma Jul 03 '18 at 15:50
  • @AkashSharma : createtime is just a field in your datafile. I cannot say how it is generated but it you open your file using a spreadsheet you'll be able to see the values. Maybe they are all NULL? – Christian Palmer Jul 04 '18 at 07:31

1 Answers1

0

add where not exists condition

INSERT INTO data_sm_metric
  (unique_id,
   metric_review_criterion_desc,
   metrics_review_criterion_value,
   entity_type,
   reviewcriteriatype)

  SELECT unique_id,
         metric_review_criterion_desc,
         metrics_review_criterion_value,
         entity_type,
         reviewcriteriatype
    FROM data_sm_metric_stg
        where not exists ( select 1 from data_sm_metric where
         stg.unique_id=d.unique_id and
          stg.METRIC_REVIEW_CRITERION_DESC=d.METRIC_REVIEW_CRITERION_DESC and
            stg.CREATETIME=d.CREATETIME
          )
Moudiz
  • 7,211
  • 22
  • 78
  • 156