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

1. Is this right approach to stop duplicates and insert into another table ?

2. 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.

Answer 1

As far as you trigger concerned there are several problems:

1. you don't have ; after insert statement
2. IF statement should end with END IF and a semicolon, not just END
3. you have to change a delimiter with DELIMITER command
4. 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:

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