Inserting multiple values in junction table

      Comments Off on Inserting multiple values in junction table

Inserting multiple values in junction table

I have a main table:

ID  first_name last_name  designation
--------------------------------------------------------
1       A1       Z1         Student
2       A2       Z2         HOD,Professor 
3       A3       Z3         Professor

I created 2 more tables (names and designation) to satisfy 1NF.

names table :

ID  first_name last_name
-------------------------
n_1   A1         Z1
n_2   A2         Z2
n_3   A3         Z3

designation table:

designation_code  designation
-----------------------------
D_101             Student
D_102             HOD
D_103             Professor

I created a junction table due to many to many relation which contains primary key of both tables. Currently my junction table is empty.

namedesignation table:

ID   designation_code
----------------------

I know we can insert the records manually but I have 1000’s of records in both the tables. How can I use INSERT query to insert millions of record in the junction table in one go so that the final result looks like this?

namedesignation table:

ID   designation_code
----------------------
n_1     D_101
n_2     D_102
n_2     D_103
n_3     D_103