Creating stored procedure for multiple insert and alter table queries

Question

$link->query("DROP TABLE IF EXISTS table2");           
$link->query("CREATE TABLE table2 (newcol BIGINT UNSIGNED  PRIMARY KEY)");
$result=$link->query("select col1 from table1");
while($data=$result->fetch_array(MYSQL_ASSOC))
{

    $link->query("insert into table2 (newcol) values($data['col1']);
    $link->query(""ALTER TABLE table2 ADD  `".$data['col1']."` BIGINT DEFAULT 0"");
  }

What Iam trying to do is

Create a table "table2" with one column "newcol".
select all the values of "col1" from "table1" And
for each value of col1 from table1

-insert the value into "newcol" of table2 And

-add a column named (value from col1 of table 1) into "table2"

The above code looks very neat and efficient in php , but the problem is it takes some amount of time .So I think its better to convert these into MySQL Stored procedure .Since I'm new to stored procedures , very much confused .Please help me guys.

Will the tables and the fields always be the same or do you intend to pass those value as input in your stored procedure? — Jonathan Parent Lévesque
– Jonathan Parent Lévesque, Commented Mar 25, 2016 at 13:11

Jonathan Parent Lévesque · Accepted Answer · 2016-03-25 14:05:49Z

1

Of course, I couldn't test it, but it is compiling fine on on my computer.

DELIMITER //

CREATE PROCEDURE `myProcedure` ()
BEGIN

    DECLARE _done BOOLEAN DEFAULT FALSE;
    DECLARE _myField BIGINT UNSIGNED DEFAULT 0;

    /* the cursor here is like your PDOStatement 
    * it is used to fetch data */
    DEClARE _myReader CURSOR FOR
        SELECT `col1` FROM `table1`;

    /* it is not very elegant, but we need to throw an exception
    * to break the loop */    
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET _done = TRUE;

    DROP TABLE IF EXISTS `table2`;

    CREATE TABLE `table2` (
        `newcol` BIGINT UNSIGNED PRIMARY KEY
    );

    /* you open your PDOStatement */
    OPEN _myReader;

    /* myLoop is like a GOTO*/
    myLoop: LOOP

        /* $result->fetch_array(MYSQL_ASSOC)*/
        FETCH _myReader INTO _myField;

        /* if the no data exception had been thrown, 
        * goto the end of the loop */
        IF _done = 1 THEN 
            LEAVE myLoop;
        END IF;


        INSERT INTO `table2` (newcol) VALUES (_myField);

        ALTER TABLE `table2` ADD `_myField` BIGINT DEFAULT 0;

    END LOOP myLoop;

    /* close your PDO object */     
    CLOSE _myReader;

END //

edited Mar 25, 2016 at 14:05

answered Mar 25, 2016 at 13:45

Jonathan Parent Lévesque

1,9601 gold badge27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sachin Over a year ago

I will test it soon . Can you add some commented details for each statements ,since it will be useful for me and others who are new to this stored procedures . Its hard to find simple examples on various operations like using cursors in stored procedures for learning purpose.

Sachin Over a year ago

much better now , have a doubt , is there any alternate for achieving this with out using cursors ?

Jonathan Parent Lévesque Over a year ago

If it was just inserting, I would have done something like INSERT INTO table2 (newcol) SELECT col1 FROM table1, but you also need to alter the table two to create the new field. I think the current solution is probably the simplest.

Jonathan Parent Lévesque Over a year ago

INSERT INTO table2 (newcol) SELECT col1 FROM table1.

Sachin · Accepted Answer · 2016-03-25 16:23:54Z

Jonathan Parent Lévesque helped me a lot in figuring out how the looping inside stored procedures work and to get the overall structure for the stored procedure equivalent to the php code described in the question above.
Thanks Jonathan Parent Lévesque
But in his code Adding column name using a variable didn't work as expected.

Finally I figured it out

BEGIN

    DECLARE _done BOOLEAN DEFAULT FALSE;
    DECLARE _myField BIGINT DEFAULT 0;

    DEClARE _myReader CURSOR FOR
    SELECT id FROM `tags`;

    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET _done = TRUE;

    DROP TABLE IF EXISTS `tag_similarity`;

    CREATE TABLE `tag_similarity` (
        `tag` BIGINT UNSIGNED PRIMARY KEY
    );

    OPEN _myReader;

    myLoop: LOOP

        FETCH _myReader INTO _myField;

        IF _done = 1 THEN 
            LEAVE myLoop;
        END IF;


        INSERT INTO `tag_similarity` (tag) VALUES (_myField);
        SET @sql = CONCAT('ALTER TABLE tag_similarity ADD `',_myfield,'` BIGINT DEFAULT 0');
      PREPARE stmt FROM @sql;
         EXECUTE stmt ;
         DEALLOCATE PREPARE stmt;
      END LOOP myLoop;

    CLOSE _myReader;

END

Collectives™ on Stack Overflow

Creating stored procedure for multiple insert and alter table queries

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related