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.
LVL 2
puneetdudejaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
asafadisConnect With a Mentor Commented:
Try this:

/* If you really want every single 'Y' value from Table2 */
INSERT INTO test.TableName (`X`,`Y`,`Z`)
SELECT '123' AS `X`, `Y`, '456' AS `Z`
FROM test.TableName2;

/* If you only want unique 'Y' values from Table2 */
INSERT INTO test.TableName (`X`,`Y`,`Z`)
SELECT '123' AS `X`, `Y`, '456' AS `Z`
FROM test.TableName2
GROUP BY `Y`;

Open in new window

0
 
asafadisCommented:
0
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
You shouldn't need a loop at all. I haven't got a MySQL install handy to verify this, but try:
INSERT INTO TableName (X, Y, Z)
   SELECT 'Dummy1', Y, 'Dummy2' FROM Table2

Open in new window

0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
puneetdudejaAuthor Commented:
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 ?

0
 
puneetdudejaAuthor Commented:

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

0
 
asafadisCommented:
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?
0
 
puneetdudejaAuthor Commented:
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.
0
 
asafadisCommented:
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

0
 
puneetdudejaAuthor Commented:
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.
0
 
asafadisCommented:
Has to be stored procedure.
0
All Courses

From novice to tech pro — start learning today.