Iterate 50 times over an SQL command

I want to make propotion codes for my product.

I have created an appropriate 开发者_运维百科SQL statement which basically uses the current timestamp and runs SHA1 on it.

I tried a while ago to create an iterative loop over my INSERT command but failed

anyone know how?

Do 50 times
   INSERT INTO ......
end

Also, I cannot have two of the same promotion code so the timestamp needs to be different for each iteration (If it is at all possible that the timestamp might be the same between iterations).

write the code in python or some other scripting language. Use a GUID for the promotion code rather than a hash of the timestamp.

http://dev.mysql.com/doc/refman/5.0/en/flow-control-constructs.html

Your approach with using the PROCEDURE is the best I guess. I would first set another delimiter, because you are using the ';' already:

delimiter // 

After that define your procedure. The INSERT INTO ...... is your INSERT code, what you wrote in your question.

CREATE PROCEDURE createPromotions(p1 INT)
BEGIN
   SET @x = 0;
   REPEAT 
   SET @x = @x + 1; 

   INSERT INTO ......

   UNTIL @x > p1 
   END REPEAT;
END
//

After entering the '//' the procedure is ready to CALL. And if you want to execute it 50 times, just call it 50 times:

CALL createPromotions(50)

This is pretty dirty, but if you have any table on your system which you know has more than 50 rows, you can do the following:

create table promotion_code ( pc varchar(100) );
set @c = 1;
insert into promotion_code 
  select sha1( now() + (@c := @c + 1 ) ) 
  from mysql.help_relation limit 50;

CREATE PROCEDURE createPromotions(p1 INT)
 BEGIN
   SET @x = 0;
   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END

Some sort of proceedure like this would be nice but I don't know how to get it into the format I need. which is to repeat an SQL statement 50 times