before insert trigger for insert duplicate rows into another table

Question

I have a table called tblspmaster in which sp column i have unique index so there will be no duplicates will be inserted, but i want to insert duplicate rows into tblspduplicate . so i decided to write trigger for this . IN master table which is tblspmaster records will be inserted using Load File of mysql

create trigger tblspmaster_noduplicate
before insert on tblspmaster
for each row
begin
  if ( select count(sp) from tblspmaster where sp=new.sp > 0 )then
    insert into tblspduplicate (sp,FileImported,AMZFileName)   values (NEW.sp,NEW.FileImported,NEW.AMZFileName)
  END
END

I have list of questions

Is this right approach to stop duplicates and insert into another table ?
My trigger is not executing as its showing some syntax errors

ERROR response is Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END END' at line 7

****************EDITED**************

here is table definition for master and duplicate table and trigger and load data file MySQL statements

CREATE TABLE IF NOT EXISTS `tblspmaster` (
  `CSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `SP` varchar(10) NOT NULL,
  `FileImportedDate` date NOT NULL,
  `AMZFileName` varchar(50) NOT NULL,
  `CasperBatch` varchar(50) NOT NULL,
  `BatchProcessedDate` date NOT NULL,
  `ExpiryDate` date NOT NULL,
  `Region` varchar(50) NOT NULL,
  `FCCity` varchar(50) NOT NULL,
  `VendorID` int(11) NOT NULL,
  `LocationID` int(11) NOT NULL,
  PRIMARY KEY (`CSN`),
  UNIQUE KEY `SP` (`SP`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10000000000 ;




   CREATE TABLE IF NOT EXISTS `tblspduplicate` (
      `SP` varchar(50) NOT NULL,
      `FileImportedDate` date NOT NULL,
      `AMZFileName` varchar(50) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

use casper;
DELIMITER $$
create trigger tblspmaster_noduplicate
before insert on tblspmaster
for each row
begin
  if ( select count(sp) from tblspmaster where sp=new.sp > 0 ) then
    insert into tblspduplicate (sp,FileImportedDate,AMZFileName)   values (NEW.sp,NEW.FileImportedDate,NEW.AMZFileName);
  END IF;
END$$

DELIMITER ;






LOAD DATA local INFILE 'E://31october//SP//sp_files_sample1//400k sp00 6-19 E.csv'
INTO TABLE  tblspmaster
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sp);

Here is one twist in story that i am executing this MySql command from a c# console application but i dont think it will affect our db related structure or program in any way. I also need to remove IGNORE 1 LINES statement as there will be no header row.

peterm · Accepted Answer · 2013-09-05 06:00:29Z

7

As far as you trigger concerned there are several problems:

you don't have ; after insert statement
IF statement should end with END IF and a semicolon, not just END
you have to change a delimiter with DELIMITER command
use EXISTS() rather then COUNT()

That being said your trigger might look like

DELIMITER $$
CREATE TRIGGER tblspmaster_noduplicate
BEFORE INSERT ON tblspmaster
FOR EACH ROW
BEGIN
  IF (EXISTS(SELECT * FROM tblspmaster WHERE sp = NEW.sp)) THEN
    INSERT INTO tblspduplicate (sp,FileImported,AMZFileName)   
    VALUES (NEW.sp, NEW.FileImported, NEW.AMZFileName);
  END IF;
END$$
DELIMITER ;

Here is SQLFiddle demo

Use IGNORE clause in your LOAD DATA INFILE statement. MySql will treat errors (violating unique constraint) as warnings effectively discarding duplicates.

LOAD DATA INFILE
If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped.

LOAD DATA LOCAL INFILE 'E://31october//SP//sp_files_sample1//400k sp00 6-19 E.csv' 
IGNORE  
INTO TABLE tblspmaster 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
-- IGNORE 1 LINES

Note: FYI failed inserts for duplicate rows will leave gaps in values of auto_increment SCN column.

You may consider another approach which might be more preferable performance wise:

create temporary staging table with no constraints and no indices
use LOAD DATA INFILE to populate staging table
having tblspmaster and the staging table and using INSERT ... SELECT syntax insert all duplicates in tblspduplicate in one go
insert only non-existent rows from staging table into tblspmaster again in one go
TRUNCATE or DROP staging table

edited Sep 5, 2013 at 6:00

answered Sep 4, 2013 at 18:41

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

Michael - sqlbot Over a year ago

Combine this with INSERT IGNORE to allow the duplicates to be discarded without throwing an error.

peterm Over a year ago

@rahularyansharma It's already in my mysql sqlfiddle example. Take a closer look. Last insert statement uses INSERT IGNORE syntax. In your case it should be LOAD DATA INFILE ... IGNORE. Otherwise the first duplicate record will throw an error.

rahularyansharma Over a year ago

@peterm I am using LOAD DATA local INFILE 'E://31october//SP//sp_files_sample1//400k sp00 6-19 E.csv' INTO TABLE tblspmaster FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (sp); for inserting records so where i can use ignore .

peterm Over a year ago

Right after the file name and before INTO clause. It's different from IGNORE X LINES clause.

rahularyansharma Over a year ago

OK i wil try this , one more thing i want to ask you that when i run first time when there is no records in master table its runs OK but when i again run this command to check trigger and values to be inserted in duplicate table . its almost stop means taking 3 hours for 400 k rows and single column value .

|

Collectives™ on Stack Overflow

before insert trigger for insert duplicate rows into another table

1 Answer 1

18 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related