SQL INSERT

SQL INSERT Statement

The INSERT statement adds a new row to a table.

INSERT can contain values for some or all of its columns.

INSERT can be combined with a SELECT to insert records.

Example

#

Problem: Add a new supplier to the database.

INSERT INTO Supplier (CompanyName, ContactName, 
                      City, Country, Phone)
VALUES ('Broodjes Huis', 'Henk de Groot', 
        'Amsterdam', 'Netherlands', '31-1-382-8847')

Syntax

INSERT syntax.

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

INSERT with SELECT syntax.

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

More Examples

SQL INSERT

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

SQL INSERT SELECT

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
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 record added

You may also like



Guides