How to insert values into a junction/linking table in SQL Server?

Question

I am piggy backing off this question regarding creating a junction/linking table. It is clear how to create a junction table, but I am concerned about how to fill the junction table with data. What is the simplest and/or best method for filling out the junction table (movie_writer_junction) with data between two other tables (movie, writer)

CREATE TABLE movie
(
     movie_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
     movie_name NVARCHAR(100),
     title_date DATE
);

CREATE TABLE writer
(
     writer_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
     writer_name NVARCHAR(100),
     birth_date DATE
);

INSERT INTO movie 
VALUES ('Batman', '2015-12-12'), ('Robin', '2016-12-12'),
       ('Charzard, the movie', '2018-12-12')

INSERT INTO writer 
VALUES ('Christopher', '1978-12-12'), ('Craig', '1989-12-12'),
       ('Ash', '1934-12-12')  

CREATE TABLE movie_writer_junction
( 
     movie_id  INT,
     writer_id INT,

     CONSTRAINT movie_writer_pk 
         PRIMARY KEY(movie_id, writer_id),
     CONSTRAINT movie_id_fk 
         FOREIGN KEY(movie_id) REFERENCES movie(movie_id),
     CONSTRAINT writer_fk 
         FOREIGN KEY(writer_id) REFERENCES writer(writer_id)
);

The final junction table is currently empty. This is a simple example, and you can manually fill the data into the junction table, but if I have two tables with millions of rows, how is something like this completed?

You use `insert` with the keys of the rows you want to insert. — Gordon Linoff, Sep 11 '18 at 16:33
Presumably, you have a source of some type that tells you which writer wrote which movie (to continue with your example) - you'd script your inserts based on that. — Brian, Sep 11 '18 at 16:35

score 2 · Answer 1 · answered Sep 12 '18 at 14:36

Hi I'm guessing this relates to the fact that you can't rely on the Identity Columns being the same in different regions.

You can write your inserts as a cross join from the 2 src tables

Insert junc_table (writer_id, movie_id)
Select writer_id , movie_id
from writer 
CROSS Join 
movie
where writer_name = 'Tolkien' and movie_name = 'Lord of the Ring'

This way you always get the correct Surrogate Key (the identity) from both tables. Its pretty easy to generate a SQL statement for all your existing junction combinations using a bit of Dynamic SQL

Another Approach is to Use SET IDENTITY_INSERT ON - but this needs to be done when loading the 2 other tables and that ship may already have sailed!

How to insert values into a junction/linking table in SQL Server?

1 Answers1