SQL INSERT INTO Statement

How do I add new records to a table?

The INSERT INTO statement is used to add new data to a database.
INSERT INTO adds a new record to a table.

INSERT INTO can contain values for some or all of its columns.
INSERT INTO can be combined with a SELECT to insert records.

The SQL INSERT INTO syntax

The general syntax is:

INSERT INTO table-name (column-names) 
VALUES (values) 

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

SQL INSERT INTO Examples

Problem: Add a new customer to the database
INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
VALUES ('Craig', 'Smith', 'New York', 'USA', 1-01-993 2800)
Result:  1 new record inserted

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Add a new customer named Anita Coats to the database
INSERT INTO Customer (FirstName, LastName)
VALUES ('Anita', 'Coats')
Result:  1 new record inserted

The SQL INSERT combined with a SELECT

The general syntax is

INSERT INTO table-name (column-names) 
SELECT column-names
  FROM table-name
 WHERE condition

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
CUSTOMER
Id
FirstName
LastName
City
Country
Phone

SQL INSERT INTO
with SELECT Example

Problem: The Bigfoot Brewery supplier has
also become a customer.  Add a customer
record with values from the supplier table.
INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1), 
       SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100), 
       City, Country, Phone
  FROM Supplier
 WHERE CompanyName = 'Bigfoot Breweries'

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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas