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`
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.
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:
CREATE PROCEDURE update
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;
FETCH cur1 INTO lf1, lf2;
-- imaging f1 is the primary key
SELECT COUNT(1) INTO count
WHERE f1 = lf1;
IF count = 0 THEN
-- record does not exist so we can perform one type of operation
-- record exists in the table so we can update or whatever
UNTIL done END REPEAT;
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.