SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT syntax

The general syntax is:

  1. INSERT INTO tablename (columnnames)
  2. SELECT columnnames
  3. FROM tablename
  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

댓글 남기기