Skip to main content
HomeAbout SQLLearn SQL

Insert Into SQL Tutorial

SQL's "INSERT INTO" statement can be used to add rows of data to a table in the database.
Oct 2020  · 3 min read

Syntax

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:

First part:

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;

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.

-- 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;

Try it for yourself.

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.

Topics

More courses on databases and SQL

Certification available

Course

Introduction to Relational Databases in SQL

4 hr
131.4K
Learn how to create one of the most efficient ways of storing data - relational databases!
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

A Comprehensive Guide to SQL Certification on DataCamp

Explore SQL certification with DataCamp's courses & tutorials. Gain practical skills & theoretical knowledge to excel in data analytics and advance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

Top 5 SQL Server Certifications: A Complete Guide

Unlock SQL Server certification success with our guide on paths, preparation with DataCamp, and the top certifications to enhance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

Mastering SQL ROUND: Precision Handling in Data Analysis

Learn how to use the SQL ROUND function in this tutorial. It covers the implementation of the ROUND function in Oracle, MySQL, PostgreSQL, and SQL Server.
Laiba Siddiqui's photo

Laiba Siddiqui

10 min

Mastering SQL NOT EQUAL Operator: A Beginner's Guide

Unlock the power of SQL NOT EQUAL with our expert guide. Learn to refine data queries with practical examples and optimization tips for better analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL CONTAINS: A Comprehensive Tutorial

Unlock the power of SQL CONTAINS for advanced text searches. Dive into logical operators, proximity searches, and wildcard uses for precise data analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

See MoreSee More