Hubert Grzeskowiak Hubert Grzeskowiak - 4 years ago 117
SQL Question

MySQL insert using continuous number

I'm copying a row in a table using this statement:

insert into Buyer (
version, creationDate, password, token, username, zip, city, lastname, firstname, preferredLanguage_id, title_id, contactEmail_id, active
) select
version, creationDate, password, token, "loadtest_1@example.com", zip, city, lastname, firstname, preferredLanguage_id, title_id, contactEmail_id, active
from Buyer where username="developer_de@example.com";


Only thing I change is the username/email. Now the number in the new username to be inserted, "loadtest_1@example.com", should increment every time. So the second should be loadtest_2..., loadtest_3 and so on. I don't really care at what number it starts as long as it's continuous, so taking the ID of the newly inserted row or the like would be totally okay.

Extra kudos for ideas on how to actually create a batch of these inserts so I don't have to run it X times.

Answer Source

You are selecting and inserting to same table and only change is username. what I see is you need a UPDATE statement rather like

update Buyer set username = 'loadtest_1@example.com'
where username="developer_de@example.com";

If it's test and you do really want to insert test data saying loadtest_1@example.com .. loadtest_100@example.com then you can use a while loop like

CREATE PROCEDURE usp_inserttestdata(total INT)
AS
BEGIN
DECLARE counter INT;
DECLARE uname varchar(20);

   SET counter = 1;

   label1: WHILE counter <= total DO

    SET uname = concat('loadtest_', counter, '@example.com');     

   insert into Buyer (
    version, creationDate, password, token, username, zip, city, lastname, 
    firstname, preferredLanguage_id, title_id, contactEmail_id, active) 
   select version, creationDate, password, token, uname, zip, city, lastname, firstname, 
   preferredLanguage_id, title_id, contactEmail_id, active
   from Buyer where username="developer_de@example.com";

   SET counter = counter + 1;

   END WHILE label1;
END

Then call the procedure saying

CALL usp_inserttestdata 1000
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download