(Tutorial) Insert Into SQL – DataCamp

[ad_1]

SQL’s “INSERT INTO” assertion can be utilized so as to add rows of knowledge to a desk within the database.

Syntax

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:

First half:

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;

Second half:

INSERT INTO table_b ...;

Take this half and append it to the primary, so it inserts all distinct rows from table_a into table_b.

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;

Try it for your self.

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.

[ad_2]

Source hyperlink

Write a comment