inserting sequential records into a table.

Hello There,
This is probably more a MySQL question than a Heidi Question:
Frequently I need to make a list of sequential entries into a table

for example

insert into tag_virtual (tag, parent, descriptor, priority, role)
values
('2001','0','Announcement 1','127','Announcement')
('2002','0','Announcement 2','127','Announcement')
('2003','0','Announcement 3','127','Announcement')
('2004','0','Announcement 4','127','Announcement')
('2005','0','Announcement 5','127','Announcement')
('2006','0','Announcement 6','127','Announcement')
('2007','0','Announcement 7','127','Announcement')
('2008','0','Announcement 8','127','Announcement')
('2009','0','Announcement 9','127','Announcement')
('2010','0','Announcement 10','127','Announcement');

The tag numbers are not always the same but they are always sequential.
Is there a simple MySQL query I could run to automate this?

This is how you could do it:

First, you create a stored procedure:

delimiter //
CREATE PROCEDURE seqInsert (i INT)
BEGIN
DECLARE count INT DEFAULT 1;
WHILE count <= i DO
INSERT INTO tag_virtual (tag, parent, descriptor, priority, role)  VALUES ('2001', '0', CONCAT('Announcement ', count), '127', 'Announcement');
SET count = count + 1;
END WHILE;
END;
delimiter ;

And then you can do this at any time:

CALL seqinsert(10);

Thankyou Simkin,
that looks very promising but the `tag` also has to be sequentially incremented and could start with any number.
initially I got the error "tag 2001 already exists" because tags must be unique.

So I found the procedure in mysql.proc and deleted it.
then did the following:

CREATE PROCEDURE seqInsert (i INT) 
BEGIN   DECLARE count INT DEFAULT 1;
WHILE count <= i DO
INSERT INTO tag_virtual (tag, parent, descriptor, priority, role) VALUES ((2000 + count), '0', CONCAT('Announcement ', count), '127', 'Announcement');
SET count = count + 1;   END WHILE; END;

and now when I do

CALL seqinsert(10);

I get

/* SQL Error: FUNCTION siteconfigDB.CONCAT does not exist */

Which I didn't get the first time. Odd.
I think I broke something :)

regards
Robert

So I found the procedure in mysql.proc and deleted it.

you physically deleted it?? :shock:

You should never, ever, delete/modify mysql files (except for things like .ini files). Only make modifications using mysql commands. In this case, you should've used:

DROP PROCEDURE seqInsert;

then did the following:

CREATE PROCEDURE seqInsert (i INT) 
BEGIN   DECLARE count INT DEFAULT 1;
WHILE count <= i DO
INSERT INTO tag_virtual (tag, parent, descriptor, priority, role) VALUES ((2000 + count), '0', CONCAT('Announcement ', count), '127', 'Announcement');
SET count = count + 1;   END WHILE; END;

and now when I do

CALL seqinsert(10);

I get

/* SQL Error: FUNCTION siteconfigDB.CONCAT does not exist */

Which I didn't get the first time. Odd.
I think I broke something :)

Did you make a backup of the file you deleted? If so, try putting back the backup.
I cannot explain why it doesn't understand the CONCAT function anymore, but i guess it's because the procedure-mechanism got corrupt.

Can you still use CONCAT in normal queries? For example:

SELECT CONCAT('from now on ', 'i will only use queries') AS promise;

So I found the procedure in mysql.proc and deleted it.

you physically deleted it?? :shock:


You should never, ever, delete/modify mysql files (except for things like .ini files). Only make modifications using mysql commands. In this case, you should've used:

DROP PROCEDURE seqInsert;

No I used HeidiSQL to delete it (which
I assume would use SQL commands?) but I'll try to remember that in future, thanks.

Can you still use CONCAT in normal queries? For example:

SELECT CONCAT('from now on ', 'i will only use queries') AS promise;

No.
Don't panic, it's only a "scratch" database I'm messing with.

Thanks for your help so far.

No I used HeidiSQL to delete it (which
I assume would use SQL commands?) but I'll try to remember that in future, thanks.

Hmmm...i have a hard time picturing what you did .... what you are saying is that somehow you deleted this mysql.proc file with Heidi? how did you do that?

No.
Don't panic, it's only a "scratch" database I'm messing with.

Thanks for your help so far.

Well, that's a good thing :)

I still don't quite understand what exactly happened though, so i'm not sure what to advise you to repair whatever is broken now.
But if CONCAT is not working, even outside stored procedures, then there seems to be a very serious problem

No I used HeidiSQL to delete it (which
I assume would use SQL commands?) but I'll try to remember that in future, thanks.

Hmmm...i have a hard time picturing what you did .... what you are saying is that somehow you deleted this mysql.proc file with Heidi? how did you do that?

What I did was
expand the mysql database in the tree on the left,
click on the proc table and then
delete the record called seqInsert

I don't know why this would cause a problem.

To fix the mysterious "CONCAT" problem I reinstalled a clean database.

aahhhh....ok

you are probably logged in as root

it's better to create a user for the database you want to work with and then log in with those credentials. especially if you don't know exactly what you're doing. as root you can do - as you found out - too much damage.

the mysql database is a special database ....it's where mysql stores all kinds of stuff that it needs in various tables. you shouldn't manually modify this data, because things might get corrupt otherwise (which is probably what happened).

If you want to have a look at how a procedure is saved you should look in the ROUTINES table in the information_schema database.

BUT, best is still just a query. In this case:
SHOW CREATE PROCEDURE procedure_name;