SQL INSERT INTO Statement

  • The INSERT INTO statement is used to add new data to a database.
  • The INSERT INTO statement 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 definitive guide
for data professionals


Order today!
 See 2 min video



The SQL INSERT INTO syntax


The general syntax is:
INSERT INTO table-name (column-names) 
VALUES (values) 




SQL INSERT INTO Examples




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


Results: 1 new record inserted




Problem: Add a new customer named Anita Coats to the database
INSERT INTO Customer (FirstName, LastName)
VALUES ('Anita', 'Coats')


Results: 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





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.
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.

Results: 1 new record inserted