Store procedure select insert

Hi,
I’m new to sp so I’d like to know
if I made everything right
(it workish insert only a record than I got a duplicate key error).


DELIMITER $$
DROP PROCEDURE IF EXISTS myactiveUser $$
CREATE PROCEDURE myactiveUser()
BEGIN
  DECLARE done INT default 0;
  DECLARE uid INT;
  DECLARE umail VARCHAR(255);
  DECLARE cur1 CURSOR FOR 
  SELECT id,email FROM my_user 
  INNER JOIN my_user_detail ON my_user.id = my_user_detail.user_id WHERE my_user.status = 'confirmed';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  FETCH cur1 INTO uid,umail;
  myloop: WHILE NOT done DO
   INSERT INTO my_user_active (
      id ,
      email
    )
    VALUES (
      uid, 
      umail
    );
  END WHILE myloop;
END; $$
DELIMITER ;

I post just an example in the realworld
I’ve a lot of fields so I’m wondering if
there is a shortcut instead of declaring
all the fields :frowning:

Bye.

EDIT
I tried with


myloop: WHILE NOT done DO
   FETCH cur1 INTO uid,umail;
   INSERT INTO my_user_active (
      id ,
      email
    )
    VALUES (
      uid, 
      umail
    );
  END WHILE myloop;

but I’ve got a duplicate key error yet

I tried it both by mysql browser and by php.

I know getting old you get lazyness :slight_smile:

why are you trying to insert one row at a time?

why aren’t you using the INSERT SELECT form of the INSERT statement?

INSERT 
  INTO my_user_active 
     ( id 
     , email )
SELECT my_user.id
     , my_user.email 
  FROM my_user 
INNER 
  JOIN my_user_detail 
    ON my_user_detail.user_id = my_user.id
 WHERE my_user.status = 'confirmed'

you probably don’t even need to bother with the stored proc…

Yeah I agree with you but
what happen if I run your
select statement you know
I get an
Duplicate entry ‘1’ for key ‘PRIMARY’
:slight_smile:
yes you get the same with may sp :slight_smile:
but I forget to empty the table before
insert all the records.

The sp is done to avoid a very
heavy query therefore need to
be run once in a week so
I need also a method to empty
the table and fill up it again.

Feel free to ask me for more info :slight_smile:

EDIT
May be I could use your statement
in the sp after empty the table ?

avoid a heavy query???

:slight_smile:

http://www.sitepoint.com/forums/showthread.php?t=717149

with that query I can retrieve up to
300,000 so php goes nut :frowning:
and I thought to a sp to build
my ‘view’.

yes, i am aware of that other thread, but you did not explain the “heavy query” over there either :slight_smile:

did you try the INSERT SELECT query i gave you above?

Just now in dev db and it took
8 secs for 130,000
and php is still alive :slight_smile:
So I could set up a little script
with your statement plus a delete
statement I got the point :slight_smile:

I don’t know if tomorrow my top dog
will be pleased with my :slight_smile:
new and cool way to work out the problem.

For that and out of curiosity (I’m a geek)
what’s the right way to make the sp (just in case) ?

EDIT
btw heavy query is actually that query
and php get down with it
break up the task I hope to work out the problem.
Do you agree ?

i don’t understand “php is still alive”

did you test my INSERT SELECT outside of php? i.e. directly in mysql?

as for rewriting your stored proc, sorry, i can’t, i’m too lazy to upgrade from mysql 4

It’s a piece of cake


DELIMITER $$
DROP PROCEDURE IF EXISTS set_my_users $$
CREATE PROCEDURE set_my_users()
BEGIN
 TRUNCATE TABLE my_user_active;
 INSERT 
  INTO my_user_active 
     ( id 
     , email )
SELECT my_user.id
     , my_user.email 
  FROM my_user 
INNER 
  JOIN my_user_detail 
    ON my_user_detail.user_id = my_user.id
 WHERE my_user.status = 'confirmed';
END; $$
DELIMITER ;

I mean for the youngsters :stuck_out_tongue:

I would like to only import data since the last import so I was thinking of my logic to be like this:

INSERT INTO Table_2
(Field1, Field2, … , FieldN)
VALUES (SELECT Field1, Field2, … , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))

Does this make sense? Or do you all suggest a different mannger of accomplishing this?