· 7 years ago · Jan 19, 2019, 05:16 PM
1CREATE TABLE IF NOT EXISTS `mydb`.`tblVariable` (
2 `idtblVariable` INT NOT NULL ,
3 `name` VARCHAR(20) NULL ,
4 `station_id` INT NULL ,
5 `description` VARCHAR(100) NULL ,
6 `id_history` INT NOT NULL DEFAULT 0 ,
7 `type` ENUM('Analog','Digital') NOT NULL
8)
9
10CREATE TABLE TEMP
11(Digit int);
12INSERT INTO Temp VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
13
14INSERT INTO tblVariable (idtblVariable)
15SELECT id
16FROM
17(
18SELECT t4.digit * 1000 + t3.digit * 100 + t2.digit * 10 + t1.digit + 1 AS id
19FROM TEMP AS t1
20 CROSS JOIN TEMP AS t2
21 CROSS JOIN TEMP AS t3
22 CROSS JOIN TEMP AS t4
23) t
24
25CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`()
26BEGIN
27 #Routine body goes here...
28DECLARE counter INT;
29DECLARE i INT;
30
31SET i = 0;
32SET counter = 10000;
33
34WHILE i < counter DO
35 INSERT INTO tblVariable (idtblVariable, name, /*Other fields*/)
36 VALUES (i, CONCAT('PT',i), /*Other values*/);
37 SET i = i + 1;
38END WHILE;
39
40END;
41
42CALL NewProc;