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.
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')
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
INSERT INTO Customer (FirstName, LastName, City, Country, Phone) VALUES ('Craig', 'Smith', 'New York', 'USA', '1-01-993 7800')
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'
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
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.
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)
file-format-options let you specify details on the file format.
No, a table should already exist before running
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.
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.