Bdtjtk

I have imported a table named book1 with column names: ID, 2018M01, 2018M02, 2018M03

I need to now import a second table named book2 with column names: ID, 2018M05, 2018M06, 2018M07

There is something similar between the column names from the two tables, i.e the prefix year 2018.

If possible I prefer not to keep writing this part of the code for each column name for each table like below

CREATE TABLE book1 (

    ID VARCHARACTER(10),

    2018M01 decimal(4,2),

    2018M02 decimal(4,2),

    2018M03 decimal(4,2)

);

as in reality the actual tables has several columns.

The code written below considers the first table with some dynamic section code, however I need some help for the second import table.

DROP TABLE IF EXISTS book1;



CREATE TABLE book1 (

    ID VARCHARACTER(10),

    2018M01 decimal(4,2),

    2018M02 decimal(4,2),

    2018M03 decimal(4,2)

);



LOAD DATA LOCAL INFILE '/Users/blytonpereira/Desktop/Book1.csv' REPLACE INTO TABLE book1 

FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' IGNORE 1 LINES;



SELECT

  GROUP_CONCAT(

    CONCAT(

      'SELECT id, ''', COLUMN_NAME, ''' as month, ', COLUMN_NAME, ' as QTY FROM t1 ') SEPARATOR ' UNION ALL ')

FROM

  `INFORMATION_SCHEMA`.`COLUMNS` 

WHERE

  `COLUMN_NAME` LIKE '2018%'

INTO @sql;



SET @query = CONCAT('select id, month, QTY from (' , @sql , ') x order by id;');



SELECT @query;



 PREPARE stmt FROM @query;

 EXECUTE stmt;

I would like to avoid explicitly defining column name for each and every import. Each import I am doing are similar but slightly different, i.e. the column names are updating each time e.g. in first table it is 2018M01..2018M02...2018M03....in second table it is 2018M02..2018M03..2018M04 . I need some help writing this second part of the code where the import of the second file is happening

This is just a suggestion, obviously with no testing and with some inherent assumptions.

Looks like you could load each file into the SAME table, and therefore avoid having dynamic column names. i.e. you are ignoring the top row which I presume has column names in it

LOAD DATA LOCAL INFILE '/Users/blytonpereira/Desktop/Book1.csv' REPLACE INTO TABLE book1 

FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' IGNORE 1 LINES;

So a "staging" could be defined like so:

CREATE TABLE book_staging (

    ID VARCHARACTER(10),

    MNTH_A decimal(4,2),

    MNTH_B decimal(4,2),

    MNTH_C decimal(4,2)

);

And you could setup a fixed query with parameters, like this

set @mnth_a = '2018M04';

set @mnth_b = '2018M05';

set @mnth_c = '2018M06';



select id, month, QTY 

from (

    SELECT id, @mthn_a as month, MNTH_A as QTY FROM book_staging WHERE MNTH_A IS NOT NULL

    UNION ALL 

    SELECT id, @mnth_b as month, MNTH_B as QTY FROM book_staging WHERE MNTH_B IS NOT NULL

    UNION ALL 

    SELECT id, @mnth_b as month, MNTH_C as QTY FROM book_staging WHERE MNTH_C IS NOT NULL

    ) x 

order by id, month

;

At the end of each cycle of loading a file the staging table can be truncated or dropped (depending on preferred approach) so you a ready for the next run.

Just as an observation, column names that are also function names or reserved words are painful to use. Personally I would not use the column name month. Similarly I would much rather use a real date like 2018-01-01 than a string like 2018M01 but there may be other reasons for using those strings I'm unaware of.