Re: How to generate new table out two other table?
Thanks very much! I hope this the informations you wanted. As was not sure how to limit the for mysqldump so generated the tables plant_information_sample, dt_sample and act_gen_raw_sample. I hope this is alright.
'plant_information', 'CREATE TABLE `plant_information` (\n `ResolutionCode` text,\n `AreaCode` text,\n `AreaTypeCode` text,\n `AreaName` text NOT NULL,\n `MapCode` text,\n `GenerationUnitEIC` text,\n `PowerSystemResourceName` text NOT NULL,\n `ProductionType` text NOT NULL,\n `InstalledGenCapacity` double DEFAULT NULL,\n `id_plant` int NOT NULL AUTO_INCREMENT,\n PRIMARY KEY (`id_plant`),\n KEY `country` (`MapCode`(500)),\n KEY `installed` (`InstalledGenCapacity`),\n KEY `production_type` (`ProductionType`(500)),\n KEY `time_intervall` (`ResolutionCode`(500)),\n KEY `cta` (`AreaTypeCode`(500))\n) ENGINE=InnoDB AUTO_INCREMENT=2154 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
'act_gen_raw', 'CREATE TABLE `act_gen_raw` (\n `DateTime` text,\n `ResolutionCode` text,\n `AreaCode` text,\n `AreaTypeCode` text,\n `AreaName` text,\n `MapCode` text,\n `GenerationUnitEIC` text,\n `PowerSystemResourceName` text,\n `ProductionType` text,\n `ActualGenerationOutput` double DEFAULT NULL,\n `ActualConsumption` double DEFAULT NULL,\n `InstalledGenCapacity` double DEFAULT NULL,\n `UpdateTime` text,\n `id_plant` int NOT NULL AUTO_INCREMENT,\n PRIMARY KEY (`id_plant`)\n) ENGINE=InnoDB AUTO_INCREMENT=110527853 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
'dt', 'CREATE TABLE `dt` (\n `txt` text,\n `d` date DEFAULT NULL,\n `t` time DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
-- MySQL dump 10.13 Distrib 8.0.28, for Win64 (x86_64)
--
-- Host: localhost Database: power_plants_raw
-- ------------------------------------------------------
-- Server version 8.0.28
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `plant_information_sample`
--
DROP TABLE IF EXISTS `plant_information_sample`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `plant_information_sample` (
`ResolutionCode` text,
`AreaCode` text,
`AreaTypeCode` text,
`AreaName` text NOT NULL,
`MapCode` text,
`GenerationUnitEIC` text,
`PowerSystemResourceName` text NOT NULL,
`ProductionType` text NOT NULL,
`InstalledGenCapacity` double DEFAULT NULL,
`id_plant` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `plant_information_sample`
--
LOCK TABLES `plant_information_sample` WRITE;
/*!40000 ALTER TABLE `plant_information_sample` DISABLE KEYS */;
INSERT INTO `plant_information_sample` VALUES ('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-ALOUMINIOF','ALOUMINIO','Fossil Gas',334,1),('PT60M','10YFI-1--------U','CTA','FI CTA','FI','44W-00000000010U','Äänekoski G1','Biomass',260,2),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WMINTMINT6---I','CET_MINT6_CA','Fossil Hard coal',195,3),('PT60M','10YCH-SWISSGRIDZ','CTA','CH CTA','CH','12W-0000000431-4','Beznau 1','Nuclear',365,4),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16W-ALQUE1-----X','Alqueva - G1','Hydro Pumped Storage',127,5),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WABO1-12345-D7','ABO¿O 1','Fossil Hard coal',341.7,6),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WTE-PARO124--W','CET_Paroseni_CA','Fossil Hard coal',133,7),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16W-ALQUE2-----Q','Alqueva - G2','Hydro Pumped Storage',127,8),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WABO2-12345-DY','ABO¿O 2','Fossil Hard coal',561.8,9),('PT60M','10YCH-SWISSGRIDZ','CTA','CH CTA','CH','12W-0000000433-Z','Beznau 2','Nuclear',365,10),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16W-ALQUE3-----J','Alqueva - G3','Hydro Pumped Storage',127,11),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16W-ALQUE4-----C','Alqueva - G4','Hydro Pumped Storage',127,12),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WTE--BRAI11--0','CetBraila_BRAI1_CA','Fossil Gas',227,13),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16W-ALQUE3-----J','Alqueva II - G3','Hydro Pumped Storage',127,14),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WABO1-12345-D7','ABOÑO 1','Fossil Hard coal',341.7,15),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WABO2-12345-DY','ABOÑO 2','Fossil Hard coal',561.8,16),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W00000ABRBO-19','ABRBO-1','Wind Offshore',99,17),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W00000ABRTW-1Z','ABRTW-1','Wind Onshore',36,18),('PT60M','10YPL-AREA-----S','CTA','PL CTA','PL','19W000000000257U','BGP Wloclawek','Fossil Gas',465,19),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W0000000ABTH7Y','ABTH7','Fossil Hard coal',535,20),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W00000BHLAW-15','BHLAW-1','Wind Onshore',108,21),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W000000ABTH7G2','ABTH7G','Fossil Oil',17,22),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W0000000ABTH8W','ABTH8','Fossil Hard coal',535,23),('PT60M','10YMK-MEPSO----8','CTA','MK CTA','MK','33W-TECBITOLAG1Q','BITOLA G1','Fossil Brown coal/Lignite',233,24),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WTE--BRAI2---3','CetBraila_BRAI2_CA','Fossil Gas',210,25),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W100000ABTH8GN','ABTH8G','Fossil Oil',17,26),('PT15M','10YRO-TEL------P','CTA','RO CTA','RO','30WBRAZBRAZ5---1','CETBrazi__BRAZ5_CA','Fossil Gas',98,27),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W0000000ABTH9U','ABTH9','Fossil Hard coal',520,28),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WBRAZBRAZ6---X','CETBrazi__BRAZ6_CA','Fossil Gas',98,29),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W000000ABTH9GX','ABTH9G','Fossil Oil',17,30),('PT60M','10YMK-MEPSO----8','CTA','MK CTA','MK','33W-TECBITOLAG2O','BITOLA G2','Fossil Brown coal/Lignite',233,31),('PT60M','10YAT-APG------L','CTA','AT CTA','AT','14W-BAA-TU-----T','Abwinden-Asten','Hydro Run-of-river and poundage',168,32),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30UBRAZBRAZ5---T','CetBrazi_BRAZ5_CA','Fossil Gas',98,33),('PT60M','10YSE-1--------K','CTA','SE CTA','SE','46WGU00000000127','Åbyverket Örebro','Biomass',106,34),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30UBRAZBRAZ6---O','CetBrazi_BRAZ6_CA','Fossil Gas',98,35),('PT60M','10YMK-MEPSO----8','CTA','MK CTA','MK','33W-TECBITOLAG3M','BITOLA G3','Fossil Brown coal/Lignite',233,36),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WCRAICRA21---Y','CetCraiova2_CRAI1_CA','Fossil Brown coal/Lignite',131,37),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WCRAICRA22---T','CetCraiova2_CRAI2_CA','Fossil Brown coal/Lignite',131,38),('PT60M','10YNO-0--------C','CTA','NO CTA','NO','50WG00000000132O','Blafallvg1 Hydro','Hydro Water Reservoir',240,39),('PT15M','10YRO-TEL------P','CTA','RO CTA','RO','30WGALATGALA3--G','CetGalati_GALA3_CA','Fossil Gas',96,40),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WGALAGALA5---Q','CetGalati_GALA5_CA','Fossil Gas',96,41),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WGALAGALA6---L','CetGalati_GALA6_CA','Fossil Gas',96,42),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0092E','BLAYAIS 1','Nuclear',910,43),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W00000CGTHW-1W','CGTHW-1','Wind Onshore',69,44),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0049F','CHASTANG 2','Hydro Run-of-river and poundage',105,45),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0093C','BLAYAIS 2','Nuclear',910,46),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0050U','CHASTANG 3','Hydro Run-of-river and poundage',106,47),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0094A','BLAYAIS 3','Nuclear',910,48),('PT60M','10YCH-SWISSGRIDZ','CTA','CH CTA','CH','12W-0000000211-M','Châtelard','Hydro Pumped Storage',107,49),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P00958','BLAYAIS 4','Nuclear',910,50),('PT60M','10YSI-ELES-----O','CTA','SI CTA','SI','28W-G-000000123U','CHE Avce elektrarna - generator','Hydro Pumped Storage',180,51),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0173E','BLENOD 5','Fossil Gas',427,52),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W00000BLKWW-1L','BLKWW-1','Wind Onshore',53,53),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRLOTR1---C','CHE_Lotru_LOTR1_CA','Hydro Water Reservoir',169,54),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRLOTR2---G','CHE_Lotru_LOTR2_CA','Hydro Water Reservoir',169,55),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W000000BLLA-1K','BLLA-1','Wind Onshore',134,56),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRLOTR3---B','CHE_Lotru_LOTR3_CA','Hydro Water Reservoir',169,57),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRLOTR1---C','CheLotru_LOTR1_CA','Hydro Water Reservoir',170,58),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WACE3-12345-CD','ACECA 3','Fossil Gas',386,59),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30UHIDRLOTR2---7','CheLotru_LOTR2_CA','Hydro Water Reservoir',169,60),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WACE4-12345-C3','ACECA4','Fossil Gas',372.6,61),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W000000BLLA-2I','BLLA-2','Wind Onshore',69,62),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRLOTR3---B','CheLotru_LOTR3_CA','Hydro Water Reservoir',170,63),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16W-ALQUE4-----C','Alqueva II - G4','Hydro Pumped Storage',127,64),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRPDFE1---R','ChePortileDeFier_PFIERI1_CA','Hydro Run-of-river and poundage',193.5,65),('PT30M','10YGB----------A','CTA','UK(National Grid) CTA','GB','48W00000ACHRW-1O','ACHRW-1','Wind Onshore',43,66),('PT60M','10YAT-APG------L','CTA','AT CTA','AT','14W-BLOCK07-L--J','Block 07 Linz','Fossil Gas',153,67),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRPDFE2---M','ChePortileDeFier_PFIERI2_CA','Hydro Run-of-river and poundage',194,68),('PT15M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRPDFE3---H','ChePortileDeFier_PFIERI3_CA','Hydro Run-of-river and poundage',194,69),('PT60M','10YPL-AREA-----S','CTA','PL CTA','PL','19W000000000007G','Adamów B1','Fossil Brown coal/Lignite',120,70),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRPDFE4---C','ChePortileDeFier_PFIERI4_CA','Hydro Run-of-river and poundage',193.5,71),('PT60M','10YNO-0--------C','CTA','NO CTA','NO','50WG00000000015S','Alta Krvg2 Hydro','Hydro Water Reservoir',110,72),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRPDFE5---7','ChePortileDeFier_PFIERI5_CA','Hydro Run-of-river and poundage',194,73),('PT60M','10YPL-AREA-----S','CTA','PL CTA','PL','19W000000000009C','Adamów B2','Fossil Brown coal/Lignite',120,74),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRPDFE6---2','ChePortileDeFier_PFIERI6_CA','Hydro Run-of-river and poundage',194,75),('PT60M','10YPL-AREA-----S','CTA','PL CTA','PL','19W000000000011P','Adamów B3','Fossil Brown coal/Lignite',120,76),('PT60M','10YPL-AREA-----S','CTA','PL CTA','PL','19W000000000013L','Adamów B4','Fossil Brown coal/Lignite',120,77),('PT60M','10YAT-APG------L','CTA','AT CTA','AT','14W-BAW-TU-----V','Altenwörth','Hydro Run-of-river and poundage',328,78),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRRIUM1---1','CheRetezat_RIUM1_CA','Hydro Water Reservoir',167.4,79),('PT60M','10YPL-AREA-----S','CTA','PL CTA','PL','19W000000000015H','Adamów B5','Fossil Brown coal/Lignite',120,80),('PT60M','10YCH-SWISSGRIDZ','CTA','CH CTA','CH','12W-0000000950-B','AET Leventina','Hydro Water Reservoir',226,81),('PT60M','10YRO-TEL------P','CTA','RO CTA','RO','30WHIDRRIUM2---W','CheRetezat_RIUM2_CA','Hydro Water Reservoir',100,82),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16WALINDO1-----5','Alto Lindoso - G1','Hydro Water Reservoir',315,83),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AGDIMI-I-8','AG_DIMITRIOS1','Fossil Brown coal/Lignite',274,84),('PT60M','10YPT-REN------W','CTA','PT CTA','PT','16WALINDO2-----Z','Alto Lindoso - G2','Hydro Water Reservoir',315,85),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AGDIMI-II7','AG_DIMITRIOS2','Fossil Brown coal/Lignite',274,86),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0056I','CHEYLAS 1','Hydro Pumped Storage',259,87),('PT60M','10Y1001A1001A796','CTA','DK CTA','DK','45W000000000034P','Amagervaerket 3','Fossil Hard coal',250,88),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AGDIMIIII5','AG_DIMITRIOS3','Fossil Brown coal/Lignite',283,89),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0057G','CHEYLAS 2','Hydro Pumped Storage',259,90),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AGDIMI-IVI','AG_DIMITRIOS4','Fossil Brown coal/Lignite',283,91),('PT60M','10Y1001A1001A796','CTA','DK CTA','DK','45W000000000113T','Amagervaerket 4','Biomass',149,92),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AGDIMI-V-6','AG_DIMITRIOS5','Fossil Brown coal/Lignite',342,93),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0106T','CHINON 1','Nuclear',905,94),('PT60M','10YES-REE------0','CTA','ES CTA','ES','18WAMBITG1-12--P','AMBITG1','Fossil Gas',252.4,95),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AISGEORG80','AG_GEORGIOS8','Fossil Gas',151,96),('PT60M','10YGR-HTSO-----Y','CTA','GR CTA','GR','29WGU-AISGEORG9Z','AG_GEORGIOS9','Fossil Gas',188,97),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0107R','CHINON 2','Nuclear',905,98),('PT30M','10YIE-1001A00010','CTA','IE CTA','IE','47W0000000001796','Aghada CT1','Fossil Gas',90,99),('PT60M','10YFR-RTE------C','CTA','FR CTA','FR','17W100P100P0108P','CHINON 3','Nuclear',905,100);
/*!40000 ALTER TABLE `plant_information_sample` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-03-09 17:14:43