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