SQL INSERT INTO SELECT

SQL INSERT 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.

Example

#

Problem: Copy all customers from Paris into the Supplier table.

INSERT INTO Supplier (CompanyName, ContactName, City, Country, Phone)
SELECT FirstName + ' Company', FirstName + ' ' + LastName, City, Country, Phone
  FROM Customer
 WHERE City = 'Paris'

Syntax

INSERT INTO SELECT syntax.

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

More Examples

SQL INSERT SELECT

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
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 added.  These are the 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

You may also like



Guides