SQL INSERT INTO Statement

The SQL INSERT INTO syntax

The general syntax is:

  1. INSERT INTO tablename (columnnames)
  2. VALUES (values)

 


 

SQL INSERT INTO Examples

Problem: Add a record for a new customer

  1. INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
  2. VALUES (‘Craig’, ‘Smith’, ‘New York’, ‘USA’, 101993 2800)

Results: 1 new record inserted


Problem: Add a new customer named Anita Coats to the database

  1. INSERT INTO Customer (FirstName, LastName)
  2. VALUES (‘Anita’, ‘Coats’)

Results: 1 new record inserted


 

The SQL INSERT combined with a SELECT

The general syntax is:

  1. INSERT INTO tablename (columnnames)
  2. SELECT columnnames
  3. FROM tablename
  4. WHERE condition

 


 

SQL INSERT INTO with SELECT Example

Problem: The Bigfoot Brewery supplier is also a customer.
Add a customer record with values from the supplier table.

  1. INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
  2. SELECT LEFT(ContactName, CHARINDEX(‘ ‘,ContactName) 1),
  3. SUBSTRING(ContactName, CHARINDEX(‘ ‘,ContactName) + 1, 100),
  4. City, Country, Phone
  5. FROM Supplier
  6. WHERE CompanyName = ‘Bigfoot Breweries’

Note: ContactName is parsed into FirstName and LastName.
Parsing takes place with built-in functions: LEFT, SUBSTRING, and CHARINDEX.
Results: 1 new record inserted