Below is the normal use case for "INSERT INTO" - where you insert values manually. It is followed by the table name and an optional list of columns that should be filled with the data. Then follows the "VALUES" keyword and the actual values you want to insert.
INSERT INTO table_name (column_a, column_b) VALUES ("value_a", "value_b");
INSERT DISTINCT Records INTO New Tables
In order to copy data from an existing table to a new one, you can use the "INSERT INTO SELECT DISTINCT" pattern. After "INSERT INTO", you specify the target table's name -
organizations in the below case.
Then you select the columns that should be copied over from the source table –
unviversity_professors in this case. You use the "DISTINCT" keyword to only copy over distinct organizations.
INSERT INTO organizations SELECT DISTINCT organization, organization_sector FROM university_professors;
Output: INSERT 0 1287
The above output shows that only 1287 records are inserted into the "organizations" table.
On the other hand, if you use "INSERT INTO SELECT", without the "DISTINCT" keyword, duplicate records would be copied over as well.
INSERT INTO organizations SELECT organization, organization_sector FROM university_professors;
Output: INSERT 0 1377
Migrating Data Into a New Table
Let's migrate the data into new tables. You'll use the following pattern:
INSERT INTO ... SELECT DISTINCT ... FROM ...;
It can be broken up into two parts:
SELECT DISTINCT column_name1, column_name2, ... FROM table_a;
This selects all distinct values in table
table_a – nothing new for you.
-- Insert unique professors into the new table INSERT INTO professors SELECT DISTINCT firstname, lastname, university_shortname FROM university_professors; -- Doublecheck the contents of professors SELECT * FROM professors;
INSERT INTO table_b ...;
Take this part and append it to the first, so it inserts all distinct rows from
One last thing: It is important that you run all of the code at the same time once you have filled out the blanks.
-- Insert unique affiliations into the new table INSERT INTO affiliations SELECT DISTINCT firstname, lastname, function, organization FROM university_professors; -- Doublecheck the contents of affiliations SELECT * FROM affiliations;
To learn more about relational databases in SQL, please see this video from our course Introduction to Relational Databases in SQL.
This content is taken from DataCamp’s Introduction to Relational Databases in SQL course by Timo Grossenbacher.
More courses on databases and SQL
← Back to tutorial