MySQL Forums
Forum List  »  Stored Procedures

Re: Stored Procedure to create table
Posted by: Peter Brawley
Date: July 14, 2022 10:42AM

The fastest bulk insert alternative by far is most always LOAD DATA INFILE. You should be able to do that with just one utility input table, then move data from there as needed.

If you for some reason must create table glut, you code works with minor mods: Prepare wants a user variable rather than a declared variable. This works ...

drop procedure if exists param_test01;
delimiter go
CREATE PROCEDURE `param_test01`(IN p_schema varchar(30), IN p_year char(4), IN p_month char(2))
BEGIN
    DECLARE t_name varchar(50);
    DECLARE k_name varchar(50);
  
    set k_name = concat('sqlt_data_1_', p_year, '_', p_month, 't_stampndx');
    
    if length(p_month) < 2 then 
      set p_month = concat('0',p_month);
    end if;

    set t_name = concat(p_schema, '.', 'sqlt_data_1_', p_year, '_', p_month);
    set @t_create = concat('create table ', t_name, ' (    -- PREPARE WANTS A USER VAR
	  `tagid` int(11) NOT NULL DEFAULT ''0'',
	  `intvalue` bigint(20) DEFAULT NULL,
	  `floatvalue` double DEFAULT NULL,
	  `stringvalue` varchar(255) DEFAULT NULL,
	  `datevalue` datetime DEFAULT NULL,
	  `dataintegrity` int(11) DEFAULT NULL,
	  `t_stamp` bigint(20) NOT NULL DEFAULT ''0'',
	  PRIMARY KEY (`tagid`,`t_stamp`),
	  KEY k_name (`t_stamp`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;');               -- WHY NOT UTF8MB4?
    -- select @t_create;                                   -- DEBUG BY UNCOMMENTING THIS LINE
    prepare stmt from @t_create;
    execute stmt;
    deallocate prepare stmt;
END;
go
delimiter ;



Edited 2 time(s). Last edit at 07/14/2022 11:14AM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Procedure to create table
55
July 14, 2022 10:42AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.