Most of the previous articles in this Stairway have dealt with selecting data from SQL Server tables. Not all applications are limited to only retrieving data from a database. Your application might need to insert, update or delete data as well. In this article, I will be discussing various ways to insert data into a table using an INSERT statement. In future Stairway articles I will be exploring updating and deleting SQL Server data.

Basic INSERT statement

There are a number of formats the INSERT statement can take. When I refer to the basic INSERT statement I’m referring a simple INSERT statement that includes a list of column names and values for each of those columns.

In order to demonstrate how to use a basic INSERT statement we need to have a table into which we can insert data. For the purpose of this article I’m going to create a very simple table name Fruit. The Fruit table will track the different varieties of fruit and the quantity of fruit boxes stored in a warehouse. My Fruit table will contain the following columns, Id, Name, Color, and Quantity. The Id column will be a integer value that I use to uniquely identifies each type of fruit. The Name column is a varchar value that contains a common name to refer to the fruit. The Color column will distinguish the different fruit colors if the particular fruit has multiple colors. And lastly, the Quantity column will track the actual number of boxes that are stored in the warehouse. If you want to run each INSERT statement in this article you will need to create my Fruit table by running the code in Listing 1:

USE tempdb;
GO
CREATE TABLE Fruit (
   Id int NOT NULL,
   Name varchar(100) NOT NULL,
   Color varchar(100) NULL,
   Quantity int DEFAULT 1);

Listing 1: Create Fruit Table

As you can see I made the Id and Name columns required fields by specifying the attribute NOT NULL. I also specified a default value for the Quantity column. The different constraints on these columns will determine how my INSERT statement can look.

The basic syntax for most INSERT statements you will write will use the following format:

  INSERT (column_list) VALUES (value_list);

Where:

• column_list contains a list of columns in the inserted row, which will have a have a specific data value supplied
• value_list contains a list of data values supplied for the columns identified in the column_list specification.

The column_list specification is only needed if the value_list doesn’t include a column value for each column in your table. Let me go through a few examples to demonstrate how to use the “column_list” and “value_list” appropriately.

For my first example I will run the INSERT statement shown in Listing 2:

INSERT INTO Fruit (Id, Name, Color, Quantity) 
   VALUES (1, 'Banana', 'Yellow', 1);

Listing 2: INSERT statement with a column_list and value_list that contains every column in table Fruit

In this INSERT statement I have provided a column_list and value_list that contains every column in the Fruit table. This statement will insert one row into my table for a fruit called “Banana”.

Alternatively, if I am providing a value for every single column in the Fruit table I can leave off the column_list specification, as the INSERT statement in Listing 3 shows.

INSERT INTO Fruit   
   VALUES (2, 'Grapes', 'Red', 15);

Listing 3: INSERT statement without column_list specification

The column list specification for an INSERT statement at minimum will need to identify a value for every column in your table that requires a value. If your table definition provides any default values, or allows null values for columns, then those columns do not need to have a value supplied. The example in Listing 4 shows an INSERT statement where I do not provided a value for Color and Quantity.

INSERT INTO Fruit (Id, Name)
   VALUES (3, 'Apples');

Listing 4: INSERT statement that doesn’t include all table columns

I don’t need to provide a value for Color because it is defined to allow NULL values. The Quantity column doesn’t need to be included because there is a default constraint associated with this column.

Occasionally you might want to insert more than one record into a table at a time. You can do that using multiple INSERT statements, or you can use the syntax in Listing 5, which uses the new SQL Server 2008 syntax for inserting multiple rows with a single INSERT statement.

INSERT INTO Fruit(Id, Name, Color, Quantity)
   VALUES (4, 'Apples', 'Red', 10),
          (5, 'Peaches', 'Green', 7),
          (6, 'Pineapples','Yellow', 5);

Listing 5: Inserting multiple records into a table with a single insert statement

In Listing 5 I inserted 3 different rows into my Fruit table with a single INSERT statement. This was accomplished by providing three different VALUES specification separated by commas. Each of these different value statements contains a different type of fruit, and will create a new row for each value.

Inserting Data into a Table using a SELECT statement

There are times when you want to insert a large number of records into a table that are based upon another record set returned from a SELECT statement. In this case it would be very cumbersome to insert records one at a time using the INSERT with values_list method as described in the prior section. Instead you can use the output of a SELECT statement as input into an INSERT statement, as my example in Listing 6 demonstrates.

INSERT INTO Fruit(Id, Name, Color, Quantity)
     SELECT 7+(6-Id),Name, 'White', Quantity FROM Fruit WHERE Id > 3
     ORDER BY Id DESC;

Listing 6: Inserting multiple records into a table using a SELECT statement

In Listing 6 I inserted 3 records into my Fruit table by selecting records for my Fruit table that have and Id greater than 3. which were derived from the existing values in the Fruit table.

Inserting Data into a Table using a Stored Procedure

There are times when a single SELECT statement is not enough to identify the records you want to insert into a table. You might have some complex logic to generate a number of rows that need to be inserted. When this is the case you can easily build a stored procedure to produce a record set that can be used to insert data into a table. In Listing 7 I have a stored procedure that generates some hybrid fruit by concatenating fruit names together. The output of the stored procedure is then used to insert records into my Fruit table.

CREATE PROC HybridFruit 
AS 
   SELECT b.Id + 9, a.Name + b.name 
   FROM Fruit a INNER JOIN Fruit b
   ON a.Id = 9 - b.Id;
GO   
INSERT INTO Fruit (Id, Name) EXECUTE HybridFruit;

Listing 7: Using a stored procedure to insert records into a table

In this example I first create my stored procedure HybridFruit. This stored procedure joins the fruit table to itself in a single SELECT statement to create my new hybrid fruit names. I then use the output of this stored procedure as input into my INSERT statement (the last statement in Listing 7). I do this by using the EXECUTE option of the INSERT statement.

Using the OUTPUT Clause

When you are inserting records into a table you can also output the inserted values. These inserted values than can be used by the calling application or subsequent TSQL code. The code in Listing 8 shows how to output inserted values so the calling application can retrieve the inserted values.

INSERT INTO Fruit(Id, Name)
   OUTPUT INSERTED.*
   VALUES (18,'Pie Cherries');

Listing 8: Using OUTPUT clause to return inserted values to calling application

This example will insert a single row into the INSERTED table using the OUTPUT clause. The “.*” notation following the word “INSERTED” tells SQL Server to output the value for every inserted column value, even those that are generated, like default values. When I run this code from a query window within SQL Server Management Studio it well return the data in the INSERTED table in the results pane. The results I got when I ran the code in Listing 8 is shown in Report 1.

Report 1: Results returned to client from OUTPUT clause

If you review the output shown in Report 1, you can see there a value for every column in my Fruit table, except the Color column that allowed nulls. Note that a value for the Quantity column is returned, even though I didn’t use this column in my original query. By using the OUTPUT clause you can obtain values for columns that are computed based on column constraints. By using the OUTPUT clause you can now obtain the values for an identify column if your table had one.

When an OUTPUT clause is used without an INTO statement as I did you cannot have an INSERT trigger defined on the table into which the rows are inserted. If you want to have a trigger on your table you need to use the INTO option associated with the OUTPUT clause. By using the INTO option of an OUTPUT clause you can retrieve the INSERTED column values into a table or table variable. Having those column values in a table allows you to build logic in your application that can process through the INSERTED data. My code in Listing 9 alters the table to make the Id column an identity column, which means it will have its value automatically generated by SQL Server during the INSERT. The code then demonstrates how to use the INTO option to return the identity value and fruit name for each row inserted into my Fruit table.

-- Alter table so Id column is now an identity   
ALTER TABLE Fruit
  DROP Column Id;
ALTER TABLE Fruit
 ADD Id int identity;
-- Create table variable to hold output from insert
DECLARE @INSERTED as TABLE (
   Id int,
   Name varchar(100));
--INSERT with OUTPUT clause
INSERT INTO Fruit (Name, Color) 
   OUTPUT INSERTED.Id, 
          INSERTED.Name 
   INTO @INSERTED  
   VALUES ('Bing Cherries','Purple'),
          ('Oranges','Orange');
          -- view rows that where inserted
SELECT * FROM @INSERTED;

Listing 9: Using OUTPUT clause to return inserted values to calling application

By looking at the code in Listing 9 you can see I first drop the original Id column and then add a new Id column as an identity column. At the time the new column is added with the identity property, values will automatically be generated for each existing row. I then create my table variable @INSERTED that will hold the column values of any rows I insert into my Fruit table. I then insert two new rows into my Fruit table. If you look at my OUTPUT clause on the INSERT statement you will see two things. First you will notice that I specifically identified the INSERTED column values I wanted to output. In my example that would be Id and Name. The second thing I did was include the INTO option, which identify that I wanted the output column values to be inserted into my table variable named @INSERTED. From this example you can see that you don’t have to OUTPUT all of the INSERTED column, but only those you need. In my example I only inserted the values Id and Name into my table variable. Lastly I selected the data that was inserted into my table variable @INSERTED. You can see the data that was placed in the table variable by reviewing the output shown in Report 2.

Report 2: Results of running Listing 9

Being able to capture the identity column values can be useful in helping you when your database design requires the identity value from a row to be used in other columns within your database.

One thing worth noting is there are times when an OUTPUT clause cannot be used. One of those times is when using the EXECUTE option of the INSERT statement. For additional information on cases where an OUTPUT clause cannot be used refer to the “OUTPUT clause” topic in books online.

Populating Data in a Table

When you want to have your application populate data into a table the INSERT should be your statement of choice. There are many different way to use the INSERT statement to populate data in a table, as I demonstrated in this article. By no means can a short article like this cover all INSERT statement options. The options I showed you are those options that are most commonly used. If you want to learn more about the all the different insert options you should refer to SQL Server’s documentation.