inserting sequential records into a table.

User, date Message
Written by Jacques
4 years ago
Category: Feature discussion
8 posts since Mon, 01 Dec 08 		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?
Written by siMKin
4 years ago
104 posts since Sun, 01 Apr 07 		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);
Written by Jacques
4 years ago
8 posts since Mon, 01 Dec 08 		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

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



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

regards
Robert
Written by siMKin
4 years ago
104 posts since Sun, 01 Apr 07

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


surprisedyou 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;



wrote: 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

wrote: /* 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;
Written by Jacques
4 years ago
8 posts since Mon, 01 Dec 08

siMKin wrote:

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


surprisedyou 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.

wrote:
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.
Written by siMKin
4 years ago
104 posts since Sun, 01 Apr 07

Jacques wrote:
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?

wrote:
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
Written by Jacques
4 years ago
8 posts since Mon, 01 Dec 08

siMKin wrote:

Jacques wrote:
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.
Written by siMKin
4 years ago
104 posts since Sun, 01 Apr 07 		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;
 

Please login to leave a reply, or register at first.