migrating mysql data with month name to a new structure

Question

CREATE TABLE `monthly1` (
  `id` int(255) NOT NULL,
  `year` int(255) NOT NULL,
  `stat_id` varchar(255) NOT NULL,
  `cat_id` varchar(255) NOT NULL,
  `january` varchar(255) NOT NULL,
  `february` varchar(255) NOT NULL,
  `march` varchar(255) NOT NULL,
  `april` varchar(255) NOT NULL,
  `may` varchar(255) NOT NULL,
  `june` varchar(255) NOT NULL,
  `july` varchar(255) NOT NULL,
  `august` varchar(255) NOT NULL,
  `september` varchar(255) NOT NULL,
  `october` varchar(255) NOT NULL,
  `november` varchar(255) NOT NULL,
  `december` varchar(255) NOT NULL,
);

Hi i have an existing mysql data with structure like above and i would like to migrate the data to a new structure like in the structure below

CREATE TABLE `monthly2` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `year` int(255) NOT NULL,
  `stat_id` varchar(255) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `monthName` varchar(255) NOT NULL,
  `monthlydata` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

how do i properly create the insert query to do this..i tried the query below but how do i set the monthName to be month name (january) instead of january data ?

INSERT INTO `monthly2` (`year`, `stat_id`, `cat_id`, `monthName`, `monthlydata`)
select `year`,`stat_id`,`cat_id`, `january`, `january`
from `monthly1 WHERE year >= '2010' AND year < '2018'

Use single quote instead of backtick.https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — P.Salmon, Feb 23 '19 at 08:13
I'm not sure that storing the name of the month is the smartest idea ever. — Strawberry, Feb 23 '19 at 08:20

score 0 · Accepted Answer · answered Feb 23 '19 at 08:33

0

First, make the year column int in table monthly1.

Use the following query to insert records in another table:-

INSERT INTO 
monthly2  
(SELECT `id`,`year`,`stat_id`,`cat_id`, 'january', `january` FROM monthly1 
WHERE year >= 2010 AND year < 2018);

FYI

I have to remove the:-

ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

for the test.

answered Feb 23 '19 at 08:33

Rakesh Jakhar

6,380
2
11
20

i tried it like this but i got duplicate entry.... INSERT INTO monthly2 (SELECT `id`,`year`,`stat_id`,`cat_id`, 'january', `january` FROM monthly1 WHERE year >= 2010 AND year < 2018); INSERT INTO monthly2 (SELECT `id`,`year`,`stat_id`,`cat_id`, 'february', `February` FROM monthly1 WHERE year >= 2010 AND year < 2018); how do i do it it full year from january to december ? – Faith Feb 23 '19 at 11:53
remove id from monthly1 select – Rakesh Jakhar Feb 23 '19 at 11:56
Column count doesn't match value count at row 1 – Faith Feb 23 '19 at 12:02
Try INSERT INTO monthly2 (SELECT '',`year`,`stat_id`,`cat_id`, 'january', `january` FROM monthly1 WHERE year >= 2010 AND year < 2018); – Rakesh Jakhar Feb 23 '19 at 12:04
Unknown column '' in 'field list' – Faith Feb 23 '19 at 12:07

score 0 · Answer 2 · answered Feb 23 '19 at 12:08

0

Try this:-

INSERT INTO 
monthly2  
(SELECT '',`year`,`stat_id`,`cat_id`, 'january', `january` FROM monthly1 
WHERE year >= 2010 AND year < 2018);

answered Feb 23 '19 at 12:08

Rakesh Jakhar

6,380
2
11
20

Unknown column '' in 'field list' – Faith Feb 23 '19 at 12:13
INSERT INTO monthly2 (`year`,`stat_id`,`cat_id`,`monthName`,`monthlydata`) SELECT `year`,`stat_id`,`cat_id`, 'january', `january` FROM monthly1 WHERE year >= 2010 AND year < 2018; – Rakesh Jakhar Feb 23 '19 at 12:33

migrating mysql data with month name to a new structure

2 Answers2