SQL INSERT INTO SELECT Statement

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

 

---

 

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