Dofactory.com
Dofactory.com

SQL INSERT INTO SELECT

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

#

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'
Result:  2 records copied

Syntax

INSERT INTO SELECT syntax.

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

More Examples

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 records inserted
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



Last updated on Dec 21, 2023