Mysql Stored Procedure

narayanyr · #1 (**permalink**) 09-05-10, 13:11

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.

it-iss.com · #2 (**permalink**) 09-06-10, 17:26

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 ;

narayanyr · #3 (**permalink**) 09-06-10, 23:52

Thanks a lot

it-iss.com · #4 (**permalink**) 09-07-10, 06:02

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

narayanyr · #5 (**permalink**) 09-07-10, 06:08

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech