🚨 Time is Running Out: Reserve Your Spot in the Lucky Draw & Claim Rewards! START NOW

Code has been added to clipboard!

SQL INSERT INTO SELECT

Reading time 1 min
Published Aug 9, 2017
Updated Oct 9, 2019

TL;DR – The SQL INSERT INTO SELECT statement can copy some or all columns from one table into another. The existing table rows are not affected.

Copying all columns to another table

Example
INSERT INTO mytable2 
SELECT * FROM mytable1 
WHERE condition;

Copying a few columns to another table

Example
INSERT INTO  mytable2 (column1, column2, column3, ...)
SELECT  column1, column2, column3, ...
FROM mytable1
WHERE condition;

Example using demo databases

The Developers table

ID Name City Country
1 Tom Kurkutis New York USA
2 Ana Fernandez London UK
3 Antonio Indigo Paris France
4 Aarav Kaelin Delhi India
5 Andrew Tumota Miami USA
DataCamp
Pros
  • Easy to use with a learn-by-doing approach
  • Offers quality content
  • Gamified in-browser coding experience
  • The price matches the quality
  • Suitable for learners ranging from beginner to advanced
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid Certificates of completion
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free certificates of completion

The Customers table

ID Name Contact Address City Postal_Code Country
1 Ben Choplinks Ben Choplink Obeesre Str. 51 Rome 11207 Italy
2 Donald Rich Donald Richario Avda. de la Confgfstitución 4122 Tallin 17021 Estonia
3 Lilly Smilkins Lilly Smilkin Matadsderos 2312 Eguero 14023 Mexico
4 Brandinina Tom Hitchins 110 Hanegover Sq. London WB2 2DP UK
5 Carizmos Christiano Kerrys Berguvsesvägen 9 Luleå S-968 43 Sweden

Copying a few columns from Customers to Developers

Example
INSERT INTO Developers (Name, Country)
SELECT Name, Country FROM Customers;

Copying the Customers table into the Developers table

Example
INSERT INTO Developers (Name, City, Country)
SELECT Name, City, Country FROM Customers;

Copying only UK Customers into the Developers table

Example
INSERT INTO Developers (Name, City, Country)
SELECT Name, City, Country FROM Customers
WHERE Country='UK';