Duplicate INSERT INTO TABLE VALUES occurs, skip that particular insert without error and go for next-CodePudding

I have a table named TABLE1 having 2 columns (NO, NAME)

 CREATE TABLE TABLE1( NO NUMBER(3) NOT NULL, NAME VARCHAR2(5) NOT NULL);
 
 CREATE UNIQUE INDEX TX ON TABLE1(NO,NAME);

I am trying to insert values as mentioned below:

INSERT INTO TABLE1 VALUES(101, 'JOHN');
INSERT INTO TABLE1 VALUES(102, 'MORN'); 
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'RONY');

As you see above, I have given one insert statement twice. Actually, above insert statements are working fine but when it comes 4th insert statement, it is throwing error which is expected. I am looking for INSERT INTO VALUES statement if duplicate insert statement comes it should skip that particular insert statement without showing error and go for next INSERT statement.

I tried using the below statements but its not working in Oracle SQL

INSERT IGNORE INTO TABLE1 VALUES(101, 'JOHN');
INSERT IGNORE INTO TABLE1 VALUES(102, 'MORN'); 
INSERT IGNORE INTO TABLE1 VALUES(103, 'TONY');
INSERT IGNORE INTO TABLE1 VALUES(103, 'TONY');
INSERT IGNORE INTO TABLE1 VALUES(103, 'RONY');

CodePudding user response：

You can use an error log and redirect errors to that:

begin
  dbms_errlog.create_error_log (
    dml_table_name     => 'TABLE1'
  );
end;
/

Then:

INSERT INTO TABLE1 VALUES(101, 'JOHN') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(102, 'MORN') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'TONY') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'TONY') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'RONY') LOG ERRORS REJECT LIMIT UNLIMITED;

Then:

SELECT * FROM table1;

Outputs:

NO NAME

101 JOHN

102 MORN

103 RONY

103 TONY

NO	NAME
101	JOHN
102	MORN
103	RONY
103	TONY

and:

SELECT * FROM ERR$_TABLE1;

Outputs:

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ NO NAME

1 ORA-00001: unique constraint (FIDDLE_TAXEGGNAZTFZWUNUZXOL.TX) violated<br> null I null 103 TONY

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	NO	NAME
1	ORA-00001: unique constraint (FIDDLE_TAXEGGNAZTFZWUNUZXOL.TX) violated<br>	null	I	null	103	TONY

db<>fiddle here

CodePudding user response：

You can use MERGE statements to check for existing rows and only insert new ones:

MERGE INTO TABLE1 dst
USING (
  SELECT 101 AS no, 'JOHN' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 102 AS no, 'MORN' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'TONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'TONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'RONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

Then:

SELECT * FROM table1;

Outputs:

NO NAME

101 JOHN

102 MORN

103 RONY

103 TONY

NO	NAME
101	JOHN
102	MORN
103	RONY
103	TONY

db<>fiddle here

CodePudding user response：

You can wrap your INSERT statements in a PL/SQL anonymous block and catch the DUP_VAL_ON_INDEX exception:

BEGIN
  INSERT INTO TABLE1 VALUES(101, 'JOHN');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(102, 'MORN');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'TONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'TONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'RONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

Then:

SELECT * FROM table1;

Outputs:

NO NAME

101 JOHN

102 MORN

103 RONY

103 TONY

NO	NAME
101	JOHN
102	MORN
103	RONY
103	TONY

db<>fiddle here

CodePudding user response：

You may use IGNORE_ROW_ON_DUPKEY_INDEX hint in your INSERT statements to skip such rows. But it works much slower than plain insert with check or error logging table.

begin
INSERT INTO TABLE1 VALUES(101, 'JOHN');
INSERT INTO TABLE1 VALUES(102, 'MORN'); 
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'RONY');
end;/

ORA-00001: unique constraint (FIDDLE_FQMLAYWWHWTRJVYNUNXG.TX) violated ORA-06512: at line 5

begin
INSERT /*  ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(101, 'JOHN');
INSERT /*  ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(102, 'MORN');
INSERT /*  ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(103, 'TONY');
INSERT /*  ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(103, 'TONY');
INSERT /*  ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(103, 'RONY');
end;/

1 rows affected

db<>fiddle here