SQL INSERT INTO SELECT Statement

How do I copy data between tables?

  • 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 SQL INSERT INTO SELECT syntax

The general syntax is

INSERT INTO table-name (column-names)
SELECT column-names 
  FROM table-name
 WHERE condition

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

SQL INSERT SELECT
INTO Example

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'
Note: LEFT, CHARINDEX, and SUBSTRING are built-in functions.
Result:  2 rows affected.
These are the two newly inserted Customer records
FirstName LastName City Country Phone
Jean-Guy Lauzon Montréal Canada (514) 555-9022
Chantal Goulet Ste-Hyacinthe Canada (514) 555-2955


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas