Mysql loop and insert

Question

I have the following MySql script:

SET @skip = 0;
SET @max = (SELECT COUNT(*) FROM table1);

CREATE TEMPORARY TABLE TempTable(
   id INT NOT NULL,
   name VARCHAR(32) NOT NULL
);

loop1: LOOP
  INSERT INTO TempTable (id, name) SELECT id, name FROM table1 LIMIT @skip, 1;

  IF @skip < @max THEN
    SET @skip = @skip + 1;
    ITERATE loop1;
  END IF;
  LEAVE loop1;
END LOOP loop1;

SELECT * FROM TempTable;

This script is not working but it should select all the id and names in table1. I am using a loop because I am also going to do other stuff in those loops but that is for later. I am not looking for a solution like SELECT id, name FROM table1 but I want my error fixed. So I can continue with my loop.

The error I get is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'loop1: LOOP INSERT INTO TempTable (id, name) SELECT id, name FROM table1' at line 1

I have no clue, I am trying to make this work, but I dont know for sure. — SynerCoder
– SynerCoder, Commented Sep 27, 2012 at 18:26

Jan Żankowski · Accepted Answer · 2017-04-06 07:28:02Z

7

/* set delimiter */
DELIMITER $$

/* remove procedure if exists... */
DROP PROCEDURE IF EXISTS insert_it $$

/* create procedure */ 
CREATE PROCEDURE insert_it ()
BEGIN
DECLARE varcount INT DEFAULT 1;
DECLARE varmax INT DEFAULT 15;

WHILE varcount <= varmax DO
    INSERT INTO yourtable(fixed_val, count_val) VALUES(3493, varcount);
    SET varcount = varcount + 1;
END WHILE;

END $$

/* reset delimiter back to normal */
DELIMITER ;

/* call procedure */ 
CALL insert_it();

edited Apr 6, 2017 at 7:28

Jan Żankowski

9,0017 gold badges42 silver badges55 bronze badges

answered Sep 18, 2014 at 12:29

Łukasz Starowicz

2012 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Solmer Over a year ago

Code only answers are usually frowned upon. Try adding an explination or code comments. Thanks.

Zsolt Szilagyi Over a year ago

While missing some explanation, I highly appreciated this A-Z solution - it worked out of the box.

ProdigyProgrammer · Accepted Answer · 2012-09-27 18:40:29Z

6

try something like this for the syntax of your loop:

DECLARE @count INT;
DECLARE @max INT;
SET @count=1;
SET @max= (SELECT COUNT(*) FROM table1);
WHILE(@count < @max)
BEGIN
    /*your database query logic*/
END

use "SET @count=(@count+1)" to increment your counter within the loop

answered Sep 27, 2012 at 18:40

ProdigyProgrammer

4151 gold badge3 silver badges14 bronze badges

1 Comment

usumoio Over a year ago

This is a good solution. Why do you explicitly use user variables here?

AndreKR · Accepted Answer · 2012-09-27 18:44:13Z

2

There is a syntax error in your code (the LIMIT @skip which is dynamic SQL and requires some tricks to make it work) but it is not at loop1: LOOP.

My guess is you are trying to use LOOP outside a compound statement (BEGIN ... END) like a stored procedure, which is not possible. You have to create a stored procedure to do that.

answered Sep 27, 2012 at 18:44

AndreKR

33.9k21 gold badges120 silver badges181 bronze badges

2 Comments

SynerCoder Over a year ago

That would most certainly be the case, is a transaction also ok to use a loop in?

AndreKR Over a year ago

No, on dev.mysql.com/doc/refman/5.5/en/… it says: Stored procedures and functions, triggers, and events.

Collectives™ on Stack Overflow

Mysql loop and insert

3 Answers 3

2 Comments

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Related