MySQL procedure to create a table each time another procedure is called (merging two scripts)

The attached code is a MySQL procedure.  It fills the row of one table (node_) with rows of another table (master_) based on a series of conditions. Here is the creation script for node_ table:

SELECT @tblname:=concat('node_',date_format(now(),'%Y%m%d%H%i%S'),'');
SELECT @cr := concat('CREATE TABLE ',@tblname,' (
  `node_id`  INT(5),
  `node_code`  VARCHAR(4),
  `node_1st`  VARCHAR(1),
  `node_2nd`  VARCHAR(1),
  `node_3rd`  VARCHAR(2),
  `node_act`  VARCHAR(1),
  `node_ref`  INT(4),
  `node_dur`  INT(4),
  `node_suc`  INT(4),
  `node_log`  VARCHAR(2),
  `node_lag`  INT(4),
  `node_con`  INT(4))','');
PREPARE stmt from @cr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Now what i need to know how to do is how to have the attached code incorporate the above creation scipt, in such that each time the attached code is called, a new table is created.

So how do I do that?
create procedure WALKTHROUGH() 
 
BEGIN 
 
DECLARE rowwalker CURSOR as select * from "master_" order by primary_key ASC; 
 
OPEN rowwalker; 


read_loop:  LOOP 

WHILE   rowwalker.PRE iS not null DO 
        fetch rowwalker; 
	IF done THEN 
   		LEAVE read_loop; 
	END IF; 
end while; 


INSERT INTO NODE_ (node_act, node_ref, node_suc, node_log, node_lag, node_con) 

           VALUES ('1', rowwalker.REF, rowwalker.SUC, rowwalker.LOG, rowwalker.LAG, rowwalker.CON); 


INSERT INTO NODE_ (node_act, node_ref, node_suc, node_log, node_lag, node_con) 
           VALUES (SELECT '1', REF, SUC, LOG, LAG, CON FROM master_ WHERE SUC = rowwalker.SUC);


IF done THEN 
   LEAVE read_loop; 
END IF; 

END LOOP; 
 
 
CLOSE rowwalker; 
 
END;

Open in new window

marcoullisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marcoullisAuthor Commented:
please note that currently the attached script incorrectly references "NODE_"

how can we make the procedure know to apply the attached code to the right table?
0
marcoullisAuthor Commented:
or is that not possible?
0
arnoldCommented:
You could place it at the top of the procedure right after begin and replace the reference to NODE_ with the variable @tblname.

Depending on the frequency with which this procedure runs, a check to see whether the table already exists as to avoid issuing create table when the table already exists

add  if not exist
create table if not exists @tblname
Presumably this is what caused the SP to abort when the creation was included due to creation table error.
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

marcoullisAuthor Commented:
could you suggest the correct code pls?
0
arnoldCommented:
Based on discussion and the need/use of variables, you may have to repeat the same method you used in the create table directive in your insert statements.
http://forums.mysql.com/read.php?98,103422,103422#msg-103422

not sure the syntax is correct, but it might give you an idea on how to implement it.

create procedure WALKTHROUGH() 
 
BEGIN 
use database_name;
Declare @tblname type varchar(30);

Declare @master_table type varchar(30); 

set @master_table:='master_tablename';

SET @tblname:=concat('node_',date_format(now(),'%Y%m%d%H%i%S'),'');
SET @cr := concat('CREATE TABLE if not exists ',@tblname,' (
  `node_id`  INT(5),
  `node_code`  VARCHAR(4),
  `node_1st`  VARCHAR(1),
  `node_2nd`  VARCHAR(1),
  `node_3rd`  VARCHAR(2),
  `node_act`  VARCHAR(1),
  `node_ref`  INT(4),
  `node_dur`  INT(4),
  `node_suc`  INT(4),
  `node_log`  VARCHAR(2),
  `node_lag`  INT(4),
  `node_con`  INT(4))','');
PREPARE stmt from @cr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
DECLARE rowwalker CURSOR as select * from "master_" order by primary_key ASC; 
 
OPEN rowwalker; 


read_loop:  LOOP 

WHILE   rowwalker.PRE iS not null DO 
        fetch rowwalker; 
	IF done THEN 
   		LEAVE read_loop; 
	END IF; 
end while; 


set @insert_node := concat("INSERT INTO ",@tblname, " (node_act, node_ref, node_suc, node_log, node_lag, node_con) 

           VALUES ('1', rowwalker.REF, rowwalker.SUC, rowwalker.LOG, rowwalker.LAG, rowwalker.CON)";
Prepare stmt from @insert_node;
execute stmt;
deallocate prepare stmt  


set @insert_node := concat("INSERT INTO ",@tblname, "  (node_act, node_ref, node_suc, node_log, node_lag, node_con) 
           VALUES (SELECT '1', REF, SUC, LOG, LAG, CON FROM ", @master_table, " WHERE SUC = rowwalker.SUC)";
prepare stmt from @insert_node;
execute stmt;
deallocate prepare stmt;


IF done THEN 
   LEAVE read_loop; 
END IF; 

END LOOP; 
 
 
CLOSE rowwalker; 
 
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marcoullisAuthor Commented:
OK i will accept the solution because a real effort was made, but it didn't work when i tried to implement it... but i don't know why. will open a new question to filter out the reason it didn't work.
0
marcoullisAuthor Commented:
A solid effort, but on implementation in Toad for MySQL it did not work. Will proceed to open a new question to filter out the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.