1

I have a many to many relation table in which I need to insert rows.

Suppose the headers Are:

Table1: Id_1 | Etc....
Table2: Id_2 | Etc....
Relation_Table: Id_1 | Id_2 | Etc.

I need to do the following:

  • Insert a new element in table 1
  • Link this new element to all elements in table 2

So, I need to add in the relation table, n rows as the following:

(id_1_new, id_2_0),
(id_1_new, id_2_1),
(id_1_new, id_2_2),
(id_1_new, id_2_3),
(id_1_new, id_2_4),
(id_1_new, id_2_5),
(id_1_new, id_2_6),....
  • Where id_1_new is known and may be entered manually
  • And id_2_n may come from select Id_2 from Table2.

How can I do this using SQL statements? Microsoft Access solutions are welcome as well.

Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
  • What do you mean by 'new element' - a new field or a new record? Use looping code in VBA to add n records. https://stackoverflow.com/questions/41817502/insert-multiple-records-with-a-date-range-in-ms-access – June7 May 17 '19 at 19:41
  • Possible duplicate of [Create Multiple Records in One Form. Only One Field Changes per Record](https://stackoverflow.com/questions/33245849/create-multiple-records-in-one-form-only-one-field-changes-per-record) – June7 May 17 '19 at 19:44
  • @June7 "New element" is new record – Daniel Möller May 17 '19 at 19:54
  • Okay, commit new record to table then run code that uses that new ID to create related dependent records. Example code in links. Make attempt and when you have code with specific issue, post question. – June7 May 17 '19 at 20:04
  • Why not `INSERT INTO table1 (Id_1, Id_2) SELECT constant_value, Id_2 FROM table2`? – Parfait May 17 '19 at 20:26
  • Possible duplicate of [INSERT data FROM sql query with one constant value in MS Access with SQL](https://stackoverflow.com/questions/46219485/insert-data-from-sql-query-with-one-constant-value-in-ms-access-with-sql) – Erik A May 17 '19 at 20:29

1 Answers1

1

you can use insert... select syntax in order to select the data from the second table to your relation table, using the new id as const in the select :

insert into Relation_table (Id_1, Id_2) 
   select 'id_1_new' as Id_1, Id_2 from Table2;
Nir Levy
  • 12,750
  • 3
  • 21
  • 38
  • This is so confusing, but it works.... I suppose it is like `select (('id_1_new' as Id_1),(Id_2 from Table2))`, is this correct? Could I try (with different results, of course) something like `select Id_1 from Table1, Id_2 from Table2`? – Daniel Möller May 17 '19 at 20:35
  • @DanielMöller think of this like that - you run the select query, and insert the results into your relation table. so As long as the select part is valid, and returns the expected columns to suite the insert, it will work – Nir Levy May 17 '19 at 20:50