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