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

Answer 1

/* 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();

Answer 2

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