mysql - before insert trigger for insert duplicate rows into another table -

i have table called tblspmaster in sp column have unique index there no duplicates inserted, want insert duplicate rows tblspduplicate . decided write trigger . in master table tblspmaster records inserted using load file of mysql

create trigger tblspmaster_noduplicate before insert on tblspmaster each row begin   if ( select count(sp) tblspmaster sp=new.sp > 0 )then     insert tblspduplicate (sp,fileimported,amzfilename)   values (new.sp,new.fileimported,new.amzfilename)   end end 

i have list of questions

1. is right approach stop duplicates , insert table ?

2. my trigger not executing showing syntax errors

error response error code: 1064. have error in sql syntax; check manual corresponds mysql server version right syntax use near 'end end' @ line 7

****************edited**************

here table definition master , duplicate table , trigger , 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 each row begin   if ( select count(sp) tblspmaster sp=new.sp > 0 )     insert 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' table  tblspmaster fields terminated ',' enclosed '"' escaped '\\' lines terminated '\n' ignore 1 lines (sp); 

here 1 twist in story executing mysql command c# console application dont think affect our db related structure or program in way. need remove ignore 1 lines statement there no header row.

as far trigger concerned there several problems:

1. you don't have ; after insert statement
2. if statement should end end if , semicolon, not end
3. you have change delimiter delimiter command
4. use exists() rather count()

that being said trigger might like

delimiter $$ create trigger tblspmaster_noduplicate before insert on tblspmaster each row begin   if (exists(select * tblspmaster sp = new.sp))     insert tblspduplicate (sp,fileimported,amzfilename)        values (new.sp, new.fileimported, new.amzfilename);   end if; end$$ delimiter ; 

here sqlfiddle demo

use ignore clause in load data infile statement. mysql treat errors (violating unique constraint) warnings discarding duplicates.

load data infile
if specify ignore, input rows duplicate existing row on unique key value skipped.

load data local infile 'e://31october//sp//sp_files_sample1//400k sp00 6-19 e.csv'  ignore   table tblspmaster  fields terminated ',' enclosed '"' escaped '\\'  lines terminated '\n'  -- ignore 1 lines 

note: fyi failed inserts duplicate rows leave gaps in values of auto_increment scn column.

---

you may consider approach might more preferable performance wise:

1. create temporary staging table no constraints , no indices
2. use load data infile populate staging table
3. having tblspmaster , staging table , using insert ... select syntax insert duplicates in tblspduplicate in 1 go
4. insert non-existent rows staging table tblspmaster again in 1 go
5. truncate or drop staging table