Insert into table1 using data from staging_table1 and table2, while using staging_table1 to get the data from table2

Goal: Insert all data into a table from staging table. Each piece of data in the staging table has 2 names which can be found in a separate table. By using the 2 two names, I want to find their respective IDs and insert them into the foreign keys of the main table.

Question: How do I insert the data from a staging table into a table while using data from the staging to query IDs from a separate table?

Example tables:

TABLE location:
id             int PRIMARY KEY,
location       varchar(255) NOT NULL,
person_oneID   int FOREIGN KEY REFERENCES people(person_id),
person_twoID   int FOREIGN KEY REFERENCES people(person_id)

​x
 
TABLE location:id             int PRIMARY KEY,location       varchar(255) NOT NULL,person_oneID   int FOREIGN KEY REFERENCES people(person_id),person_twoID   int FOREIGN KEY REFERENCES people(person_id)​

TABLE staging_location:
id             int PRIMARY KEY,
location       varchar(255) NOT NULL,
p1_full_name   varchar(255) NOT NULL,
p2_full_name   varchar(255) NOT NULL

 
TABLE staging_location:id             int PRIMARY KEY,location       varchar(255) NOT NULL,p1_full_name   varchar(255) NOT NULL,p2_full_name   varchar(255) NOT NULL​

TABLE people:
person_id   int PRIMARY KEY,
first_name  varchar(255) NOT NULL,
last_name   varchar(255) NOT NULL,
full_name   varchar(255) NOT NULL,

 
TABLE people:person_id   int PRIMARY KEY,first_name  varchar(255) NOT NULL,last_name   varchar(255) NOT NULL,full_name   varchar(255) NOT NULL,​

This question was the closest example to what I have been looking for. Though I haven’t been able to get the query to work. Here is what I’ve tried:

INSERT INTO location(id,location,person_oneID,person_twoID)
     SELECT (l.id,l.location,p1.person_oneID,p2.person_twoID)
     FROM staging_location AS l 
     INNER JOIN people p1 ON p1.full_name = l.p1_full_name
     INNER JOIN people p2 ON p2.full_name = l.p2_full_name

 
INSERT INTO location(id,location,person_oneID,person_twoID)     SELECT (l.id,l.location,p1.person_oneID,p2.person_twoID)     FROM staging_location AS l      INNER JOIN people p1 ON p1.full_name = l.p1_full_name     INNER JOIN people p2 ON p2.full_name = l.p2_full_name​

Additional info: I would like to do this in the same insert statement without using an update because of the number of locations being inserted. I’m using staging tables as a result of importing data from csv files. The csv file with people didn’t have an ID field, so I created one for each person by following steps similar to the first answer from this question. Please let me know if any additional information is required or if I can find the answer to my question somewhere I haven’t seen.

Answer

Use this code even though I do not know what your data structure is and a duplicate field may be inserted

INSERT INTO location(id,location,person_oneID,person_twoID)
     SELECT (l.id,l.location,p1.person_id as person_oneID,p2.person_id as person_twoID)
     FROM staging_location AS l 
     INNER JOIN people p1 ON p1.full_name = l.p1_full_name
     INNER JOIN people p2 ON p2.full_name = l.p2_full_name

 
INSERT INTO location(id,location,person_oneID,person_twoID)     SELECT (l.id,l.location,p1.person_id as person_oneID,p2.person_id as person_twoID)     FROM staging_location AS l      INNER JOIN people p1 ON p1.full_name = l.p1_full_name     INNER JOIN people p2 ON p2.full_name = l.p2_full_name​

Advertisement

Answer