Use the INSERT statement is to add rows to a SQL Server data table. In this article we explore how to use the INSERT statement. We discuss best practices, limitations, and wrap-up with several examples.
This is the second article in a series of articles. The first is Introduction to SQL Server Data Modification Statements.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. Are you Getting Started Using SQL Server the right way? If not, do so with free tools using my free guide.
Table of contents
Before we Begin
Though this article uses the Adventure Works database for its examples, I’ve decided to create an example table for use within the database to help better illustrate the examples. You can find the script you’ll need to run here.
SQL INSERT Statement Basic Structure
The INSERT statement’s main purpose is to add rows to a table. Though an insert statement inserts data from many sources, such as literal values or source vales, the basic format is the same.
There are three components to an SQL INSERT statement:
- The table receiving new rows.
- The columns to populate
- The source data for new rows.
The general format for the Insert Statement IS:
INSERT INTO tableName
(column1, column2, …)
VALUES (value1, value2, …)
We’re now going to do some sample inserts, so if you haven’t done so already, run the script to create the esqlSalesPerson table.
SQL INSERT INTO – Inserting Single Row
In this example we insert a single row into the esqlSalesPerson table. Here is its table structure:
Let’s assume we want to insert a new sales person into the table. The command to run is:
INSERT INTO dbo.esqlSalesPerson
(FullName, SalesLastYear,
City, rowguid)
VALUES ('George Hitson', NULL,
'Midland', '794310D2-6293-4259-AC11-71D96689A3DD')
Notice we didn’t specify the SalesPersonID in the column list. This is an identity column and automatically populated.
You can switch around the columns; they don’t have to be in order. Also notice not all the columns are specified:
INSERT INTO dbo.esqlSalesPerson
(City, FullName,
rowguid)
VALUES ('Traverse City', 'Donald Sax',
'F6E26EFD-5838-40F8-ABB3-D487D2932873')
Creates the following row:
Notice that since SalesLastYear wasn’t specified, it is NULL:
Enclose column values with parenthesis. This represent one rows worth of data. To insert more than one row, just include another set of column values. Just be sure to separate each set with a comma as below:
INSERT INTO dbo.esqlSalesPerson (City, FullName, rowguid)
VALUES ('Bay City', 'Ralph Gitter',
'DED7DB59-7149-47DD-8D8F-D5FCFFF11124'),
('Alpena', 'Mike Orange',
'94600A1E-DD83-4ACE-9D59-8CD727A2C83E')
Before we continue on with more complicated example, it’s important to step back and consider the SQL INSERT statement’s behavior and some best practices.
Using Insert: Considerations and Best Practices
Data Type Considerations (padding)
Keep in mind that when inserting data into columns whose data type is CHAR, VARCHAR, or VARBINARY, the padding or truncation of data depends upon the SET ANSI_PADDING setting.
Inserting the value ‘Kris’ into a field defined as CHAR(10) results in a value padded with six additional spaces. The value inserted is ‘Kris ‘The padding rule can be confusing when using INSERT INTO, so please check out this article for specifics.
Error handling
You can handle errors when executing an INSERT INTO statement using a TRY…CATCH construct.
There are several common reasons an INSERT statement may fail. Some of the common ones are:
- Unique Key Violation – you’re trying to insert a record into a table which causes a duplicate key value.
- Primary Key Violation – you’re trying to insert a record into a table which already has a row with the same primary key.
- Foreign Key Violation – your trying to insert a row into a “child” table, yet the “parent” doesn’t exist.
- Data Type Conversion – You’re trying to insert a row, where one of the values can’t be correctly convert into the corresponding columns data type.
In these cases, the INSERT statement execution stops and the INSERT generates an error and no rows are inserted into the table.
Keep in mind you can modify this “all or nothing” behavior for arithmetic errors. Consider a divide by zero error.
INSERT INTO myNumbers (x, y)
VALUES (10/0, 5),
(10/5, 2),
(10/2, 5)
This calculation generates an error if SET ARITHABORT is set to ON. In this case, the inserted is stopped, no rows are inserted, and an error is thrown.
However, if SET ARITHABORT is set to OFF and ANSI_WARNINGS are OFF, then the same statement will successfully complete.
When it is set to OFF, an error sets the result to NULL.
For example,
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
INSERT INTO myNumbers (x, y)
VALUES (10/0, 5),
(10/5, 2),
(10/2, 5)
adds three rows with the values
When adding rows to tables it is important to understand there are some columns requiring special handling.
INSERT SELECT and Unique Identifiers
When adding data to column declared with the uniqueidentifier type use the NEWID() function to generate a globally unique value.
As an example
INSERT INTO dbo.esqlSalesPerson
(City, FullName, rowguid)
VALUES ('Traverse City', 'Donald Sax', NEWID())
Inserts a new row into the esqlSalesPerson. If you run the command again, another row is added, but the rowguid value is different.
NEWID() generates a new value each time it is called.
Identity Column Property
A new value is generated for the column whenever a row is inserted into a table with a identity column property. Because of this, since esqlSalesPerson.SalesPersonID is an identity column, we don’t specify it in our INSERT statement. The identity value increments each time a row is added.
If you try to insert a row using your own value, you’ll throw an error.
The INSERT statement
INSERT INTO dbo.esqlSalesPerson
(SalesPersonID, City, FullName, rowguid)
VALUES (9999,'Traverse City', 'Donald Sax', NEWID())
Generates the error
Cannot insert explicit value for identity column in table 'esqlSalesPerson' when IDENTITY_INSERT is set to OFF.
To get around this you can SET IDENTITY_INSERT ON
SET IDENTITY_INSERT esqlSalesPerson ON;
INSERT INTO dbo.esqlSalesPerson
(SalesPersonID, City, FullName, rowguid)
VALUES (9999,'Traverse City', 'Donald Sax', NEWID())
Runs with no errors thrown.
Default Values and other
When inserting rows, any columns not specified are provided a value by the DBMS; otherwise the row is not loaded.
The DBMS automatically provides values for columns if:
- the column is an IDENTITY column (see above)
- a default value is specified and no other value is specified.
- the column is null-able. Here the DBMS sets the column to NULL.
- The column is commutable. Here the calculation is used.
If a value isn’t provided by the statement and the engine is unable to provide a value, the row cannot be inserted. This typically happens with a value is missing and the column is NOT NULL.
Inserting Data from Other Tables
You can also use the INSERT INTO SELECT statement to insert one or more rows from one table into another. The results of a SELECT feed into the INSERT INTO.
The general form is
INSERT INTO targetTable (column1, column2, …)
SELECT (column1, column2, …)
FROM sourceTable
Let’s assume the Adventure Works sales manager would like to create a SalesPerson table and only include sales people who’s last year’s sales were greater than $1,000,000.
Insert with Inner Join
To populate this table, you could run the following INSERT SELECT:
INSERT INTO esqlSalesPerson
(FullName, SalesLastYear, rowguid)
SELECT P.FirstName + ' ' + P.LastName, S.SalesLastYear, NEWID()
FROM Sales.SalesPerson S
INNER JOIN Person.Person P
ON P.BusinessEntityID = S.BusinessEntityID
WHERE S.SalesLastYear > 1000000
In order for this to work properly the columns returned from the SELECT statement have to be in the same order as specified int the INSERT column list. In this example notice that rowguid is a required field. To populate this value, we use the NEWID() function.
You can also use a common table expression to define the rows to insert.
Insert with CTE
You can also write the example as a CTE (Common Table Expression):
WITH topSalesPerson (FullName, SalesLastYear, rowguid)
AS (
SELECT P.FirstName + ' ' + P.LastName, S.SalesLastYear, NEWID()
FROM Sales.SalesPerson S
INNER JOIN Person.Person P
ON P.BusinessEntityID = S.BusinessEntityID
WHERE S.SalesLastYear > 1000000 )
INSERT INTO esqlSalesPerson
(FullName, SalesLastYear, rowguid)
SELECT FullName, SalesLastYear, rowguid
FROM topSalesPerson
Though there is more typing, I like the CTE method. I think it makes the INSERT statement easier to read.
Remember, when using SELECT statement to insert data into another table it is best practice to first just run the SELECT statement as is to ensure you are selecting the correct rows. Also, always develop and test your code! I highly recommend using a development copy of your database.
Read More: SQL UPDATE Statement >>
