alter table statment to insert duplicate into another table

Question

I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows :

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 ;

Now i want that if anybody tries to insert duplicate record then that record should be inserted into a secondary table name tblDuplicate.

I have gone through this question MySQL - ignore insert error: duplicate entry but i am not sure that instead of

INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;

can i insert duplicate row into another table ?

what changes needed to be done in main table scheme or index column ?

**Note : Data will be inserted by importing excel or csv files and excel files generally contains 500k to 800 k records but there will be only one single column **

Can you use a stored procedure to do inserts/updates instead of direct SQL statements? If so the stored procedure can handle the logic of which table to insert into — lc.
– lc., Commented Sep 3, 2013 at 17:29
Note : Data will be inserted by importing excel or csv files from a window service . any idea how can i implement that ? — rahularyansharma
– rahularyansharma, Commented Sep 3, 2013 at 17:44

DwB · Accepted Answer · 2013-09-03 18:02:26Z

1

I believe you want to use a trigger for this. Here is the MySQL reference chapter on triggers.

Use a before insert trigger. In the trigger, check if the row is a duplicate (maybe count(*) where key column value = value to be inserted). If the row is a duplicate, perform an insert into your secondary table.

answered Sep 3, 2013 at 18:02

DwB

38.6k11 gold badges62 silver badges86 bronze badges

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

4 Comments

rahularyansharma Over a year ago

Excel file may be have around 500k records and in one day may be 20-30 files will be inserted . Is that trigger solution is feasable

DwB Over a year ago

trigger does not mean slow. The trigger will run for each insert. The slow portion will be the code that executes when the trigger executes, so try to make that efficient.

rahularyansharma Over a year ago

I use following command LOAD DATA local INFILE 'E://31october//Anuj taneja - 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); to insert csv file. now if i run the same file again then all entries should be go in to a secondary table .

DwB Over a year ago

the code to which I referred was the code in the trigger that you will be writting.

Collectives™ on Stack Overflow

alter table statment to insert duplicate into another table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related