Migrate data with INSERT INTO SELECT DISTINCT
Now it's finally time to migrate the data into the new tables. You'll use the following pattern:
INSERT INTO ...
SELECT DISTINCT ...
FROM ...;
It can be broken up into two parts:
First part:
SELECT DISTINCT column_name1, column_name2, ...
FROM table_a;
This selects all distinct values in table table_a
– nothing new for you.
Second part:
INSERT INTO table_b ...;
Take this part and append it to the first, so it inserts all distinct rows from table_a
into table_b
.
One last thing: It is important that you run all of the code at the same time once you have filled out the blanks.
This exercise is part of the course
Introduction to Relational Databases in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Insert unique professors into the new table
___ ___ professors
SELECT DISTINCT firstname, lastname, university_shortname
FROM ___;
-- Doublecheck the contents of professors
SELECT *
FROM ___;