SQL INSERT Statement for SQL Server

INSERT Statement

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.

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:

  1. The table receiving new rows.
  2. The columns to populate
  3. 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:

Insert Statement Example Table

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:

Insert Statement Example 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.

Considerations

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 reason 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.  No rows are inserted into the table, even rows

This “all or nothing” behavior can be modified for arithmetic errors.   Consider a divide by zero error.

INSERT INTO myNumbers (x, y)
VALUES (10/0, 5),
(10/5, 2),
(10/2, 5)

Will generate an error if SET ARITHABORT is set to ON.  In this case the inserted is stopped, no rows are inserted, and an error thrown.

However, if SET ARITHABORT is set to OFF and ANSI_WARNINGS are OFF, then the same statement will successfully complete.  Where there is a mathematical error, the result is replaced with 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

insert-statement-default-value

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.  Each time a row is added the identity value is incremented by one.

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 cannot be 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. In this case it is set 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.

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.

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.

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

>