SQL INSERT INTO SELECT Statement ~ niit post

• 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: 

1.  INSERT INTO table-name (column-names)

2.  SELECT column-names 

3.  FROM table-name

4.  WHERE condition

CUSTOMER

Id

FirstName

LastName

City

Country

Phone

SUPPLIER

Id

CompanyName

ContactName

City

Country

Phone

Fax

SQL INSERT SELECT INTO

Problem: Copy all Canadian suppliers into the Customer table 

1.  INSERT INTO Customer (FirstName, LastName, City, Country, Phone)

2.  SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1) AS FirstName, 

3.  SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100) AS LastName, 

4.  City, Country, Phone

5.  FROM Supplier

6.  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