SQL INSERT

An 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

#

An INSERT statement that adds 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

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

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'

ContactName is parsed into FirstName and LastName. Parsing takes place with built-in functions: LEFT, SUBSTRING, and CHARINDEX.

Result:  1 record added

Developers also Ask


How do I INSERT multiple rows in SQL?

Generally, INSERT statements are used to insert a single row.

However, it can also be used to insert multiple rows in a single operation, like so.

INSERT INTO table-name (column-list)
  VALUES (value-list-1),
  VALUES (value-list-2),
  ...
  VALUES (value-list-n)

If speed is not an issue, this approach works for a dozen or so rows.

To INSERT larger numbers of rows, an alternative approach needs be considered.

Alternatives include BCP and BULK INSERT.

What is BCP in SQL Server?

BCP is a utility that helps with data migration and data loading.

BCP stands for Bulk Copy Program.

It's a standalone program that copies bulk data between SQL Server and a file.

BCP can import large numbers of new rows into a SQL Server tables.

And it can export large numbers of rows from tables into external files.

What is BULK INSERT in SQL Server?

BULK INSERT is a command in SQL Server.

It imports data from an external data file into a table.

The syntax for BULK INSERT is as follows:

BULK INSERT table-name
FROM full-qualified-file-name
WITH (file-format-options)

The file-format-options let you specify details on the file format.

Does BULK INSERT create a table?

No, a table should already exist before running BULK INSERT.

How do I INSERT a CSV data file in SQL?

BULK INSERT allows you to import CSV data files.

BCP also supports importing data from CSV files.

Finally, SMSS (SQL Server Management Studio) also supports importing CSV and Excel data files.

What is the difference between BCP and BULK INSERT?

BULK INSERT is uni-directional.

It only imports data from a file into a SQL Server table.

BCP is bi-directional.

It can import and export data between a file and a SQL Server table.

Also, BCP supports a QUERYOUT argument that let you export records conditionally.

Both operations are very fast, but if speed is critical, then BCP can be the better choice.


You may also like



Guides


vsn 3.1