Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://stackoverflow.com/questions/812437/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.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.