puneetdudeja
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
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 ?
ASKER
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 ;
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?
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?
ASKER
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.
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...
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;
ASKER
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.
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.
http://www.1keydata.com/sql/sqlinsert.html