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 am using following procedure to do some duplicate checking before inserting into master table from staging table and if there any duplicate records, these records should be moved to duplicate table instead of master table.

CREATE  PROCEDURE `SP_MOVE_STAGING_TO_MASTER`()
BEGIN

    //creating index on staging table to make sure inner join to find duplicate records bit speed up 
    CREATE INDEX ind_staging
    ON tblstaging (SP);

    //find duplicate records and insert them into duplicate table 
    insert INTO tblspduplicate
    SELECT T2.SP,T1.FileImportedDate,T2.XYZFileName FROM  tblspmaster T1
    INNER JOIN tblstaging T2 ON T1.SP=T2.SP;

    //creating temporary table to hold the duplicate records Id which need to be deleted from staging table 
    CREATE TEMPORARY TABLE Temp (id int); 

    //insert into temp table duplicate records Id.  
    INSERT INTO Temp SELECT T2.CSN FROM  tblspmaster T1
    INNER JOIN tblstaging T2 ON T1.SP=T2.SP;

    //delete duplicate records from staging table 
    delete from tblstaging where CSN IN(
    SELECT id FROM  Temp
    );

    //insert into master table remaining records(after deleted duplicated records)
    insert INTO tblspmaster(SP,FileImportedDate,XYZFileName,XYZBatch,BatchProcessedDate,ExpiryDate,Region,FCCity)
    SELECT T2.SP,T2.FileImportedDate,T2.XYZFileName,T2.XYZBatch,T2.BatchProcessedDate,T2.ExpiryDate,T2.Region,T2.FCCity 
    FROM  tblstaging T2;

    //blank the tblstaging so next time no old records. 
    truncate table tblstaging;
    ALTER TABLE tblstaging DROP INDEX ind_staging;
END$$
DELIMITER ;

This stored procedure is not throwing any error but is locked in an infinite state ..

It may be there's some problem with my staging table schema so my table schema as follow

CREATE TABLE `tblstaging` (
  `CSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `SP` varchar(50) NOT NULL,
  `FileImportedDate` date NOT NULL,
  `XYZFileName` varchar(50) NOT NULL,
  `XYZBatch` 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`),
  KEY `ind_staging` (`SP`)
) ENGINE=InnoDB AUTO_INCREMENT=458746 DEFAULT CHARSET=latin1;

In the master table there will be millions of records, and at once in the staging table there will be a max of 800 k records.

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.