Loop unique insert into temporary table MySql stored procedure

Question

I have two tables that look like this:

Table 1
    Type 1 | Type 2 | Type 3 | ...
       1   |   3    |    0   | ...

Table 2
    Type 1 | Type 2 | Type 3 | ...
       3   |   2    |    1   | ...

I would like to combine them into a temporary table like this:

Temporary Table
UID |  Type  | Table
 1  | Type 1 |   1
 2  | Type 2 |   1
 3  | Type 2 |   1
 4  | Type 2 |   1
 7  | Type 1 |   2
 8  | Type 1 |   2
 9  | Type 1 |   2
10  | Type 2 |   2
11  | Type 2 |   2

Essentially, the numbers in tables 1 and 2 are totals and I want to break them out into individual rows in this temporary table.

I started going down the path of selecting from both tables and storing the values into temporary variables. I was then going to loop through every single variable and insert into the temporary table. But I have about 15 columns per table and there has got to be an easier way of doing this. I just don't know what it is.

Does anyone have any insight on this? My knowledge is incredibly limited on MySql stored procedures.

sgeddes · Accepted Answer · 2018-06-13 03:14:26Z

1

Not sure of an easy way to do this. One option would be to have a numbers table. Heres a quick approach to getting 1-10 in a common-table-expression (change as needed).

Then you could join to each table and each type, using union all for each subset. Here is a condensed version:

with numbers as (select 1 n union all select 2 union all 
   select 3 union all select 4 union all select 5 union all
   select 6 union all select 7 union all select 8 union all
   select 9 union all select 10)
select 'type1' as type, '1' as tab
from numbers n join table1 t on n.n <= t.type1
union all 
select 'type2' as type, '1' as tab
from numbers n join table1 t on n.n <= t.type2
union all
select 'type1' as type, '2' as tab
from numbers n join table2 t on n.n <= t.type1
union all 
select 'type2' as type, '2' as tab
from numbers n join table2 t on n.n <= t.type2

Demo Fiddle

answered Jun 13, 2018 at 3:14

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

snovosad54 Over a year ago

Welp, I don't understand most of this, but I appreciate the demo and the keywords. I will start looking into 'union all' and 'join' for tables and decipher this. Thank you!

snovosad54 Over a year ago

I should add the ‘WITH’ clause only works in MySQL 8.0.

Collectives™ on Stack Overflow

Loop unique insert into temporary table MySql stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related