Inserting multiple values in junction table-CodePudding

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

CodePudding user response：

insert into name_designation_table
select
    n.ID,
    d.designation_code
from
    main_table m
    inner join name_table n on n.first_name = m.first_name and n.last_name = m.last_name  
    inner join designation_table d on d.designation = m.designation

But this approach has a flaw — the combination of the first_name and last_name values may be non unique in your main_table. There can be more than one person with the same first and last names. You have to find more columns to uniquely identify a person, something like date of birth or social security number.

CodePudding user response：

In modern versions of SQL Server, you can use CROSS APPLY STRING_SPLIT for this

INSERT name_designation (ID, designation_code)
SELECT m.ID, d.designation_code
FROM main m
CROSS APPLY STRING_SPLIT(m.designation, ',') s
JOIN designation d on d.designation = s.value;

You can then get rid of main as all information is now stored in the other three tables.