SQL INSERT INTO SELECT Statement

  • INSERT INTO SELECT copies data from one table to another table.
  • INSERT INTO SELECT requires that data types in source and target tables match.
The definitive guide
for data professionals


Order today!
 See 2 min video



The SQL INSERT INTO SELECT syntax


The general syntax is:
INSERT INTO table-name (column-names)
SELECT column-names 
 FROM table-name
WHERE condition




SQL INSERT SELECT INTO




Problem: Copy all Canadian suppliers into the Customer table


INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1) AS FirstName, 
       SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100) AS LastName, 
       City, Country, Phone
  FROM Supplier
 WHERE Country = 'Canada'

LEFT, CHARINDEX, and SUBSTRING are built-in functions.


Results: 2 rows affected.

These are the two new Customer records

FirstName LastName City Country Phone
Jean-Guy Lauzon Montréal Canada (514) 555-9022
Chantal Goulet Ste-Hyacinthe Canada (514) 555-2955