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 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)
     then 
     update baseline set status=status where id=no;
     else insert into  tname(id,status) value(no,status);
     end if;
     end$$

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

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
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 ??? –  user21546 Oct 21 '13 at 6:11
    
well i m done with automation as well ...thanks for your advice ... :) –  user21546 Oct 24 '13 at 8:23

Your Answer

 
discard

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

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