2

I'm using MySQL and I have this kind of table:

table: historic_v1
id | student_id | level1_start_date | level1_score | level2_start_date | level2_score
1  | 2345       | 2016-02-10        | 7.5          | 2016-04-15        | 5
2  | 5634       | 2016-03-05        | NULL         | NULL              | NULL
3  | 4885       | 2015-12-02        | 6            | 2016-01-05        | NULL

I want to do an Insert in table historic_v2 with a Select in historic_v1, but I only want to have a row in historic_v2 if the value of the selected field in historic_v1 is not NULL.

I mean, the historic_v2 table needs to be like this:

table: historic_v2
id | student_id | level | key        | date       | score
1  | 2345       | 1     | start date | 2016-02-10 | NULL
2  | 2345       | 1     | score      | NULL       | 7.5
3  | 2345       | 2     | start date | 2016-04-15 | NULL
4  | 2345       | 2     | score      | NULL       | 5
5  | 5634       | 1     | start date | 2016-03-05 | NULL
6  | 4885       | 1     | start_date | 2015-12-02 | NULL
7  | 4885       | 1     | score      | NULL       | 6
8  | 4885       | 2     | start_date | 2016-01-05 | NULL

In this example, the student 2345 will have 4 rows in the historic_v2 table, the student 5634 only 1 row and the student 4885 3 rows.

How can I do this Insert with Select?

P.S.: I know that this structure is not nice, but it's just a small example of what I really need to do, and the solution of this problem will help me a lot.

Thanks in advance!

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Thiago Bittencourt
  • 442
  • 5
  • 17
  • You could try to use an event or trigger – Kode.Error404 Jun 28 '16 at 14:45
  • you want insert (more or less) a row in historic_v2 for each column in historic_v1 ? – ScaisEdge Jun 28 '16 at 14:54
  • What is the logic behind how many rows have each student? Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Jun 28 '16 at 15:00
  • I don't get how difficult to understand is my question for you. In my second paragraph I said that I want to insert into _historic\_v2_ not _NULL_ fields from _historic\_v1_, so the logic behind "how many rows have each student" is the number of not _NULL_ fields in _historic\_v1_ table the user has. – Thiago Bittencourt Jun 28 '16 at 15:52

2 Answers2

2

You can use a select union 

insert into historic_v2 (student_id, level, key , date , score)
select student_id, 1, 'start date', level1_start_date, null
from historic_v2
where level1_start_date is not null 
union 
select student_id, 1, 'score', null, level1_score
from historic_v2
where level1_score is not null 
union 
select student_id, 2, 'start date', level2_start_date, null
from historic_v2
where level2_start_date is not null 
union 
select student_id, 2, 'score', null, level2_score
from historic_v2
where level2_score is not null
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

Similar thread: INSERT with SELECT

You can work off something like this..

INSERT INTO historic_v2 (...)
SELECT ...
FROM historic_v1
WHERE ...
Community
  • 1
  • 1
brunzzy
  • 61
  • 8