i have a table with these columns and the values are coming in these tables from a csv file at a regular interval of n minutes ( by cron the load infile command) :

 ID (PK)        status      first      last     value
 1                Q          12         15       low
 2                Q          12         15       low
 3                O          12         15       High

now whenever next data comes , it should first check whether ID is present in table , if it is then it simply update that ID's corresponding field , and if new ID come , it simply insert the data.

For this i have created a procedure :

     create procedure test.testproc (IN no int , IN status varchar(10))     
     begin if exists (select id from test.testproc where id = no)
     update baseline set status=status where id=no;
     else insert into  tname(id,status) value(no,status);
     end if;

Now what i want to know :

  1. should i create two tables for this i.e one is a temporary table and one main table ? ** i'm 100% confuse here what should i do here

  2. how should i automate all these i.e data comes from a csv file , then my load infile script runs, then my above procedure should execute and checking that if ID already present it will simply update else it will insert.

OR , if you have any other process , please tell

1 Answer 1


I think first you can load infile to MEMORY temporary table. And then just:

INSERT INTO tname (id, status) SELECT id, status FROM tname_temp
  ON DUPLICATE KEY UPDATE tname.status=tname_temp.status;
TRUNCATE TABLE tname_temp;

INSERT ... SELECT syntax reference.

Of course id field has to have unique key and your csv files has to fit in memory.

MEMORY engine tables are very fast so you should have just tiny overhead to load data in two steps. As one of drawbacks they have just table level locks so their use is limited. But that does not impact this data load scenario.

  • ya..while designing algorithm for same , i found temp table will be the best solution for it...thanks for this...but how will i automate all process ??? Oct 21, 2013 at 6:11
  • well i m done with automation as well ...thanks for your advice ... :) Oct 24, 2013 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.