SQL INSERT INTO SELECT Statement

How do I copy data between tables?

INSERT INTO SELECT copies data from one table to another table.
This operation 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