Get all ID's from one table and insert values in another table based on it

Question

I have two tables:

Table 1: Companies (id, name) Table 2: Holidays (id, companyId, name)

Currently companies table have data, but holidays doesn't. I want to iterate through all companies, get their ID's and insert two records in holidays for every company. So this would be before and after:

Before:

Companies
| id | name  |
|  0 | test1 |
|  1 | test2 |

Holidays: Empty table

After:

Companies
| id | name  |
|  0 | test1 |
|  1 | test2 |

Holidays:
| id | companyId | name     | 
|  0 |         0 | holiday1 |
|  1 |         0 | holiday2 |
|  2 |         1 | holiday1 |
|  3 |         1 | holiday2 |

score 1 · Answer 1 · answered Jun 30 '19 at 08:05

1

Assuming Holidays.id is set to auto increment:

insert into Holidays (select id as companyId, 'holiday1' as name from Companies);
insert into Holidays (select id as companyId, 'holiday2' as name from Companies);

answered Jun 30 '19 at 08:05

phpd

551
4
10

1

You cannot force an auto incremented value to start from zero : [related answer](https://stackoverflow.com/a/16232681/5841306) – Barbaros Özhan Jun 30 '19 at 08:45
Yeah. I have trouble with this. How can I avoid that? it says column count doesnt match value count if I do not provide any value for auto increment, eventhough the id column is set as AI – robtot Jun 30 '19 at 08:47

Barbaros Özhan · Answer 2 · 2019-06-30T08:52:55.620

1

You need a self-join for Companies table and an iteration logic to produce id column values for Holidays. So consider using :

insert into Holidays(id,company_id,name)
select @rn := @rn + 1, c1.id, concat('Holiday',(c1.id+1))
  from Companies c1
  join Companies c2
  join (select @rn := -1) as q_iter;

Demo

edited Jun 30 '19 at 08:52

answered Jun 30 '19 at 08:35

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 3 · answered Jun 30 '19 at 12:11

I think you want:

insert into holidays (companyId, name)
    select c.companyId, h.name
    from companies c cross join
         (select 1 as ord, 'holiday1' as name union all
          select 2 as ord, 'holiday2'
         ) h
    order by c.companyId, h.ord;

This assumes that holidays.id is an auto-incremented column. If not, you should make it one. If not, though, you can use row_number():

insert into holidays (id, companyId, name)
    select row_number() over (order by c.companyId, h.ord),
           c.companyId, h.name
    from companies c cross join
         (select 1 as ord, 'holiday1' as name union all
          select 2 as ord, 'holiday2'
         ) h
    order by c.companyId, h.ord;

Or a parameter:

insert into holidays (id, companyId, name)
    select (@rn := @rn + 1) as id,
           c.companyId, h.name
    from companies c cross join
         (select 1 as ord, 'holiday1' as name union all
          select 2 as ord, 'holiday2'
         ) h cross join
         (select @rn := 0) params
    order by c.companyId, h.ord;

Get all ID's from one table and insert values in another table based on it

3 Answers3