(Tutorial) Insert Into SQL – DataCamp
SQL’s “INSERT INTO” assertion can be utilized so as to add rows of knowledge to a desk within the database.
Below is the traditional use case for “INSERT INTO” – the place you insert values manually. It is adopted by the desk identify and an elective listing of columns that needs to be full of the info. Then follows the “VALUES” key phrase and the precise values you wish to insert.
INSERT INTO table_name (column_a, column_b) VALUES ("value_a", "value_b");
INSERT DISTINCT Records INTO New Tables
In order to repeat information from an current desk to a brand new one, you should utilize the “INSERT INTO SELECT DISTINCT” sample. After “INSERT INTO”, you specify the goal desk’s identify –
organizations within the beneath case.
Then you choose the columns that needs to be copied over from the supply desk –
unviversity_professors on this case. You use the “DISTINCT” key phrase to solely copy over distinct organizations.
INSERT INTO organizations SELECT DISTINCT group, organization_sector FROM university_professors;
Output: INSERT 0 1287
The above output reveals that solely 1287 data are inserted into the “organizations” desk.
On the opposite hand, should you use “INSERT INTO SELECT”, with out the “DISTINCT” key phrase, duplicate data could be copied over as effectively.
INSERT INTO organizations SELECT group, organization_sector FROM university_professors;
Output: INSERT 0 1377
Migrating Data Into a New Table
Let’s migrate the info into new tables. You’ll use the next sample:
INSERT INTO ... SELECT DISTINCT ... FROM ...;
It could be damaged up into two components:
SELECT DISTINCT column_name1, column_name2, ... FROM table_a;
This selects all distinct values in desk
table_a – nothing new for you.
-- Insert distinctive professors into the brand new desk 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 half and append it to the primary, so it inserts all distinct rows from
One last item: It is necessary that you simply run the entire code on the similar time upon getting stuffed out the blanks.
-- Insert distinctive affiliations into the brand new desk INSERT INTO affiliations SELECT DISTINCT firstname, lastname, perform, group FROM university_professors; -- Doublecheck the contents of affiliations SELECT * FROM affiliations;
To be taught extra about relational databases in SQL, please see this video from our course Introduction to Relational Databases in SQL.
This content material is taken from DataCamp’s Introduction to Relational Databases in SQL course by Timo Grossenbacher.