Link to home
Start Free TrialLog in
Avatar of puneetdudeja
puneetdudejaFlag for India

asked on

How can Iinsert multiple records through loop in a table in MYSQL.

If I have a table like::

TableName(X, Y, Z) and I have "Y" values in Table2(Y,P).
I want to insert one record in "TableName" table, for each value of "Y" in Table2, the remaining columns of "TableName' table having same values for all the inserted records except "Y" having different values which are from "Table2".

I don't know how to apply loops in mysql.
ASKER CERTIFIED SOLUTION
Avatar of asafadis
asafadis
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of puneetdudeja

ASKER

These all solutions are alright and work.

But I also want to know the loops solution.

I have tried the loop in code snippet and it did the work.

But if I execute that loop from (BEGIN to END) statement without using any stored procedure, I get error.

Can I not execute that code without creating and calling a stored procedure ?


DELIMITER $$

DROP PROCEDURE IF EXISTS `u2me`.`curdemo`$$

CREATE DEFINER=`u2me`@`%` PROCEDURE `curdemo`()
BEGIN
 DECLARE done INT DEFAULT 0;
  DECLARE a INT;
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT MemberId FROM tblMEMMembers WHERE MemberId!=2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO a;
    IF NOT done THEN
INSERT INTO tblNTWNetwork(MemberId,NetworkMemberId,GroupId,Blocked,AddedDate,BlockedDate) 
VALUES(2,a,0,'N',NOW(),NOW());
	
       END IF;
         UNTIL done END REPEAT;
  CLOSE cur1;
    END$$

DELIMITER ;

Open in new window

What's your rationale?

If you're trying to run this from a script, then your better off running the the query we suggested.  If you insist on using your SQLT query, it would have to be in a stored procedure.

At the end of the day, it's your call... but just out of curiosity, why do you insist on using this 24-line SQLT query instead of using a much simpler query?
I am asking this because very often I need to fill master tables with some dummy data which needs looping.

e.g. I need a column named "Count" to be filled with counting from 1 to 100.

I cannot do this with the above queries.
Fair enough...
In the attached snippet, I'm populating X with a hard-coded value, Y with the same Y value from TableName2, and Z with a counter.

Check it out...

DELIMITER $$
CREATE PROCEDURE myProc()
DETERMINISTIC

BEGIN
  DECLARE counter INT DEFAULT 0;
  DECLARE done INT DEFAULT 0;
  DECLARE a, b INT;
  DECLARE cur1 CURSOR FOR SELECT '123' AS `X`, `Y` FROM test.TableName2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  REPEAT
    SET counter=counter+1;
    FETCH cur1 INTO a,b;
    IF NOT done THEN
       INSERT INTO test.TableName (`X`, `Y`, `Z`) VALUES (a,b,counter);
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
END$$

DELIMITER ;
CALL myProc();

DROP PROCEDURE myProc;

Open in new window

Can I not use this code without declaring it in a stored procedure ?

Is it necessary to call a stored procedure for doing this ?

Even if I use a DECLARE statement outside of SP, I get an error.
Has to be stored procedure.