Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    30

    Unanswered: Mysql Stored Procedure

    Hi All,
    I am trying to migrate data from a temporary table to the actual table.I am using simple insert query.I want to write in a procedure to check if the data for the fields that are being migrated exist in their parent tables.By doing so I can migrate data as and when I receive it,after it is checked if exists in the parent tables.The data I am importing is in a csv format.

    Query I am using now is

    INSERT INTO `table`
    SELECT field1,field2
    FROM temp_table
    WHERE column_name = 'A'

    Now I need help to migrate data checking if the values exist in another table

    Kindly help me with a sample script to do so.Thanks in Advance.
    Last edited by narayanyr; 09-06-10 at 03:06.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You will need to look into creating a stored procedure for this operation. Here is a small sample of what you would need to do:

    Code:
    DELIMITER $$
    CREATE PROCEDURE update
    BEGIN
     DECLARE done int DEFAULT 0;
     DECLARE cur1 CURSOR FOR SELECT f1, f2 FROM tmptable;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     DECLARE lf1 VARCHAR(255);
     DECLARE lf2 VARCHAR(255);
     DECLARE count INT;
    
     OPEN cur1;
     REPEAT 
       FETCH cur1 INTO lf1, lf2;
       -- imaging f1 is the primary key
       SELECT COUNT(1) INTO count
       FROM livetable
       WHERE f1 = lf1;
       IF count = 0 THEN
         -- record does not exist so we can perform one type of operation
       ELSE
         -- record exists in the table so we can update or whatever
       END IF;
     UNTIL done END REPEAT;
     CLOSE cur1;
    END$$
    DELIMITER ;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    30
    Thanks a lot

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    I have created a small post with an explanation of what can be done in a stored procedure. I have used a basic example of order information being transferred to a warehouse database. The data needs to be merged in with the existing data. I have included code for all of this.

    MySQL – merging external data IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2010
    Posts
    30
    Thanks Ronan .I really appreciate your concern for the problem I posted.....the link is of great help,I think I must be able to use the sample in my code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •