27 September 2010

Working with the INSERT statement in SQL Server

The INSERT statement in SQL Server is versatile. It now allows the insertion of multiple rows of literal values. It also provides the output clause that solves a number of common problems such as ascertaining the value of identity fields, and other calculated fields in tables, or for auditing the rows that are added to tables. Robert, once again, gives a clear introduction.

The INSERT statement lets you add one or more rows to a table or view in a SQL Server database. The statement is one of the primary data modification language (DML) statements available in Transact-SQL, along with UPDATE, MERGE, and DELETE. You can use the INSERT statement to add data that you specifically define, or you can add data that you retrieve from other tables or views. You can also include an OUTPUT clause in your INSERT statement to capture the statement’s results for auditing or verification purposes.

In this article, I explain how to use the INSERT statement to add data to SQL Server tables. The examples I show are based on the AdventureWorks2008 sample database, installed on a local instance of SQL Server 2008. However, you can run most of these examples against the AdventureWorks database in SQL Server 2005, with minor modifications, such as changing the database name. Where appropriate, I note which features are not supported in 2005.

NOTE: One of the views I reference in the AdventureWorks2008 database (the SalesPerson view) includes the BusinessEntityID column. This column is referred to as the SalesPersonID column in the AdventureWorks database.

Performing a Basic Insert

In a basic INSERT statement you must specify the name of the target table and the data values you want to insert into that table. When necessary, you should qualify that table name with the name of the server, database, or schema.

To demonstrate how to create a basic INSERT statement, I first used the following code to create the SalesStaff1 table in the AdventureWorks2008 database:

The table itself is fairly straightforward, and so is the INSERT statement necessary to add a row of data. The statement requires the INSERT keyword, the name of the target table, the VALUES keyword, and the values to be inserted, as shown in the following example:

Notice that the statement begins with the INSERT keyword, followed by the INTO keyword. The INTO keyword is optional. Often you’ll see it used. Often you will not. After the INSERT keyword and optional INTO keyword, you specify the name of the target table, which in this case is SalesStaff1.

Next you specify the VALUES keyword and then the values themselves. Notice that the values are enclosed in parentheses and separated by commas. In addition, string values are enclosed in single quotation marks. The values are inserted into the table in the order they’re specified in the clause. That means the values must be in the same order as the columns are defined in the table. In this case, 1 is inserted into the table’s first column (StaffID), Stephen is inserted into the second column (FirstName), and Jiang is inserted into the third column (LastName).

That’s all there is to inserting a row into a table. However, as is often the case, you might want to add multiple rows to a table in a single statement. Prior to SQL Server 2008, this was not possible, but now the INSERT statement let’s you specify multiple rows, as shown in the following example:

As you can see, you must still specify the INSERT keyword, the name of the target table, and the VALUES keyword. However, instead of a single set of parentheses, you now have a set for each row to be inserted into the table. The value sets are then separated with commas. Now when you run the statement, all four rows will be added to the SalesStaff1 table.

Inserting Specific Column Values

The examples in the previous section are INSERT statements at their simplest. They assume that you will add one value to each column in the table for each row you plan to insert. However, it’s not always that easy. In many cases, you’ll want to insert values into specific columns, but not all columns. For example, if you have an IDENTITY column, you might not want to insert a value into that, but instead allow the database engine to generate the next value automatically. However, the INSERT statement, as it is used in the previous examples, requires one value for each column. If you do not specify an appropriate value for each column, you will generate an error.

To address this issue, the INSERT statement supports an additional component that lets you specify which columns should receive values. The values you specify are then inserted into those columns. Columns that are not specified are assumed to generate their own values, as is the case with IDENTITY columns, calculated columns, or columns for which a default value has been defined.

For example, the following code creates the SalesStaff2 table, which includes columns that do not require values to be explicitly added:

As you can see, StaffID is an IDENTITY column, FullName is a calculated column, and ModifiedDate has been defined with a default value (retrieved through the GETDATE function). Now let’s look at an INSERT statement that provides values for the other three columns-StaffGUID, FirstName, LastName:

Notice that I add the list of columns after I specify the INSERT INTO keywords and the name of the target table. The columns are enclosed in parentheses and separated with commas. The data that I specify in the VALUES clause must then correspond to these columns. In this case, the VALUES clause includes three values. The first is the NEWID function, which generates a GUID for the StaffGUID column. The second value, Stephen, is inserted into the FirstName column, and the third value, Jiang, is inserted into the LastName column.

As you can see, I do not include the column names or values for any of the columns whose values are generated automatically. I can include a value for the StaffID column, if the database is configured to allow me to override IDENTIY values. I can also include a value to override the default ModifiedDate value. In either case, I must include the name of the column after the INSERT clause and include the value in the VALUES clause, in the correct order.

As you’ve no doubt noticed, I’ve added a SELECT statement after the INSERT statement in the example above. The statement returns the values that were generated automatically when I inserted the rows, as shown in the following results:







Stephen Jiang

2010-09-21 07:56:59.487

That’s all there is to adding data to specific columns. You simply provide the name of the columns that should receive values and then you specify those values. You can specify the column names in any order. Just be sure that the data in the VALUES clause corresponds to the column order. Now let’s look at what happens when you don’t explicitly define your values.

Retrieving Data from External Tables

In the previous examples, the VALUES clause includes a set of values that are enclosed in parentheses and separated by commas. But you don’t always have to explicitly specify the values. You can instead retrieve the values through a SELECT statement or through a stored procedure. To demonstrate how this works, I first created the SalesStaff3 table, shown in the following code:

Suppose I want to insert data into the SalesStaff3 table that I retrieve from the Sales.vSalesPerson view in the AdventureWorks2008 database. Instead of specifying a VALUES clause, I can specify a SELECT statement that retrieves data directly from the vSalesPerson view, as shown in the following example:

You can replace the VALUES clause with any valid SELECT statement that returns the appropriate data. In this case, I’m retrieving the BusinessEntityID values and names of the top five salespeople, based on BusinessEntityID. After the INSERT statement, I run a SELECT statement to confirm that the data has been inserted into the SalesStaff3 table, as shown in the following results:





Stephen Jiang

2010-09-21 08:15:48.927


Michael Blythe

2010-09-21 08:15:48.927


Linda Mitchell

2010-09-21 08:15:48.927


Jillian Carson

2010-09-21 08:15:48.927


Garrett Vargas

2010-09-21 08:15:48.927

A SELECT statement that’s used within an INSERT statement can reference a common table expression (CTE) as well as a table or view, as long as that CTE precedes the INSERT statement (as opposed to being part of the SELECT statement itself). For instance, in the following example, I first define a CTE and then define the INSERT statement, which pulls data from the CTE through a SELECT statement:

Notice that the CTE retrieves data from the vSalesPerson view. The SELECT statement in the INSERT statement then retrieves the contents of the CTE, as it would retrieve data directly from a table. That data is then inserted into the SalesStaff3 table. The final SELECT statement confirms that the data has been added, as shown in the following results:





Tsvi Reiter

2010-09-21 08:26:04.340


Pamela Ansman-Wolfe

2010-09-21 08:26:04.340


Shu Ito

2010-09-21 08:26:04.340


José Saraiva

2010-09-21 08:26:04.340


David Campbell

2010-09-21 08:26:04.340

Instead of using a SELECT statement to retrieve data, you can call a stored procedure in your INSERT statement. For instance, the following stored procedure-spSalesStaff-retrieves data from the vSalesPerson view:

I can then call this stored procedure from within an INSERT statement, as shown in the following example:

Notice that in place of a VALUES clause or SELECT statement, I’ve included an EXECUTE (EXEC) statement that calls the spSalesStaff stored procedure. When I run the INSERT statement, the procedure is executed and the returned data is inserted into the SalesStaff3 table. The following results confirm that the data has been inserted:





Tete Mensa-Annan

2010-09-21 08:36:30.207


Syed Abbas

2010-09-21 08:36:30.207


Lynn Tsoflias

2010-09-21 08:36:30.207


Amy Alberts

2010-09-21 08:36:30.207


Rachel Valdez

2010-09-21 08:36:30.207

As you can see, inserting data through a SELECT statement or stored procedure provides a simple way to add multiple rows to a table, without having to explicitly define each value.

Adding an OUTPUT Clause

Starting with SQL Server 2005, DML statements supported the OUTPUT clause, which lets you output the modified table to another table or variable. In the case of the INSERT statement, you can use the OUTPUT clause to track the data that is being inserted into your table. For example, the following INSERT statement uses the OUTPUT clause to output the inserted values to a table variable:

I first declare the @InsertOutput variable and configure it with two columns: StaffID and FullName. These two columns correspond to the same two columns in the SalesStaff3 table used in the examples in the previous section.

After I declare the variable, I define the INSERT statement, which uses a SELECT statement to retrieve data from the vSalesPerson view. The INSERT statement also includes an OUTPUT clause, which follows the INSERT clause and list of columns. The OUTPUT clause captures the data inserted into the StaffID and FullName columns and saves them to the @InsertOutput variable.

NOTE: For a full discussion of the OUTPUT clause, see the Simple-Talk article “Implementing the OUTPUT Clause in SQL Server 2008” (http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/).

After the INSERT statement, I include a SELECT statement to verify that the data was inserted into the SalesStaff3 table. The statement returns the following results:





Jae Pak

2010-09-21 08:49:03.983


Ranjit Varkey Chudukatil

2010-09-21 08:49:03.983

I also include a second SELECT statement to retrieve the contents of the @InsertOutput, after the outputted data has been saved to the variable. The following table shows the contents of the variable:




Jae Pak


Ranjit Varkey Chudukatil

That’s about all there is to implementing an OUTPUT clause in your INSERT statement. In fact, that’s about all there is to working with the INSERT statement. Note, however, that the INSERT statement supports a few other features. So be sure to check out the topic “INSERT (Transact-SQL)” in SQL Server Books Online. You can also find additional examples of the INSERT statement in the topic “INSERT Examples (Transact-SQL)” in SQL Server Books Online.


We have supplied the full-size railroad diagrams for the INSERT and OUTPUT clauses as PDF files which can be downloaded below.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter


This post has been viewed 246623 times – thanks for reading.

Tags: , , , ,

  • Rate
    [Total: 232    Average: 4/5]
  • Share

Robert Sheldon

View all articles by Robert Sheldon

  • thensley

    Keep up the great work!
    This is a great series of articles you have going. It’s amazing how you can overlook new features that have been added to old friends like INSERT. It always pays to circle back and review the basics now and then.

  • MilesT

    INSERT article
    I agree completely with thensley: “It’s amazing how you can overlook new features that have been added to old friends like INSERT. It always pays to circle back and review the basics now and then.”
    This article is a job-well-done! Very well written and understandable. Thank You Robert!

  • gangadhargawande

    I find this article very interesting. Sometimes we are aware about the new feature but dont implement and just overlook them and go for other expensive alternatives.

    This is very simiple, straightforward and daily used stuff which we must use on a daily basis wherever applicable. Thanks Robert!

  • William Ross

    Great article, thanks.

  • Zubair Ahmed

    Brilliant article – short and worthy.

    Keep up the good work!!

  • www.simple-talk.com

    Postes this to my blog also. Greetings from the Speedy DNS

  • Richard Waddell

    I’ll be using what I learned here as early as tomorrow. Thanks for essential knowledge I glossed over or never saw.

  • Carlos B. Vasquez

    More Kudos
    very Interesting article about INSERT Transact-SQL Statement.

  • PhilG

    SQL 2005
    Sadly, the insertion of multiple rows of literal values doesn’t work until you reach the heady heights of SQL Server 2008

  • Siddhesh

    Get Insert Values
    I know about Ouput Clause, but I dont know about how to store the values in output clause to another table. Thanks for giving example.


  • Anonymous

    Thank God for helicopters 🙂

  • kvansh

    Good stuff
    I’m from Oracle background and reading this can appreciate SQL Server’s capabilities which I thought were not possible before.
    Few things that are actually bit difficult in Oracle are so simple in SQL Server.

    Thanks for the article.

  • cgg

    SQL Form and Database
    Thank you for the info, however, I have a form that I am populating a database. I can’t seem to find anywhere online a SQL statement that would allow me to take what was entered into a form field and place it into a database

    I am using the following statement, but it seems to keep giving me errors

    INSERT INTO TableName (SR_Instructor) VALUES ("::SR_Instructors::")

    Am I missing something?

  • schenpls

    I am already using the examples for inserting my own rows in my own tables.