Before you can read data from your database you need to understand how to insert data into the tables in your database. There are a number of different ways to accomplish this. In this article I will be explore the methods you can use when inserting data into a database table using the INSERT statement.
Syntax for the Basic INSERT Statement
There are a number of different ways to use the INSERT statement to place data into a SQL Server database table. My reference to the basic INSERT statement means inserting data into a table by using a list of columns and a value for each column. Here is the syntax for the basic INSERT statement:
INSERT INTO <object> (<column_list>) VALUES (<value_list>);
<object> - represents a table or view name
<column_list> - represents a list of columns that data will be placed when inserting a row
<value_list> - represents a list of column values that will be used to populate columns in
the column_list when inserting a row
The column_list is only needed if you don’t plan to insert values for each column in the table. If you plan to identify a value in the value_list for each column in the table then you can exclude the column list.
Creating a Table to Populate
In order to demonstrate the INSERT statement I first need to create a table that I can use to populate with data. I will use the following script to create my table:
USE tempdb; GO CREATE TABLE Sailboat ( ID int identity NOT NULL, Manufacture varchar(50) NOT NULL, Model varchar(30) NOT NULL, Length varchar(20) default 'unknown' NOT NULL, Beam varchar(20) default 'unknown' NOT NULL, Price money NULL);
This table will be used to store a list of sailboats that are for sale. The ID column is an identity field, which will be used to uniquely identify each Sailboat record. The Manufacture column will be used to store the company that built the sailboat. The Model column is used to store the model name of the sailboat. The Length and Beam columns will be used to store the overall length and beam of the sailboat in feet and inches. If the Length or Beam is not known at the time a record is inserted into this table the Length or Beam values will default to the value “unknown”. The Price field is used to store the selling price for the sailing vessel. Also note that the ID, Manufacture, Model, Length and Beam columns have the criteria to be NOT NULL, meaning these columns will need a value assigned when inserting a row into the Sailboat table. The Price column is the only column that does not require a value and it has the “NULL” property associated with that column. These NULL, NOT NULL and default constraints will determine which columns will need to be supplied on the basic INSERT statement. If a column is defined as NOT NULL and there isn’t a default column value then you will need to provide a value on your INSERT statement.
Inserting Data in My Sailboat Table
For my first example let me insert one row into my Sailboat table using the code below:
INSERT INTO Sailboat(Manufacture, Model, Length, Beam, Price) VALUES('Cal Jenson', 'Cal 40', '40 feet 0 inches','10 feet 2 inches', 39500.00);
This INSERT statement contained a column_list that identifies every column except the ID column. Every column name was separated by a comma. The value_list contained a value for each column that was listed in the column_list. The reason the ID column was excluded was because it is an identity column.
Inserting an Identity Column Value
You cannot include an identify column in the column_list unless IDENTITY_INSERT is turned on. I can demonstrate this by running the following code:
INSERT INTO Sailboat(ID,Manufacture, Model, Length, Beam, Price) VALUES(2,'Pacific Seacraft', 'Dana 24', '24 feet 0 inches','8 feet 2 inches', 50000.00);
When I run this code I get the following error:
Msg 544, Level 16, State 1, Line 15
Cannot insert explicit value for identity column in table 'Sailboat' when IDENTITY_INSERT is set to OFF.
By default the IDENTITY_INSERT is set to OFF. If I want to insert an identity column value I can turn IDENTITY_INSERT ON for my table, as I have done in the following example INSERT statement:
SET IDENTITY_INSERT Sailboat ON; INSERT INTO Sailboat(ID,Manufacture, Model, Length, Beam, Price) VALUES(2,'Pacific Seacraft', 'Dana 24', '24 feet 0 inches','8 feet 2 inches', 50000.00); SET IDENTITY_INSERT Sailboat OFF;
Excluding the Column List
If I am providing an insert statement that contains all of the columns in my table then I can exclude the column list from my INSERT statement. Below is a similar example to my first INSERT statement, but this time I have left off the column list:
INSERT INTO Sailboat VALUES('Catalina', 'Catalina 27', '26 feet 11 inches','9 feet 2 inches', 6499.00);
You may be asking yourself how come I can leave off the column list when I am not including the identity column value. When a table has an identity column that is the one column that is required to be excluded from the column list of the INSERT statement, without the IDENTITY_INSERT being set ON. Therefore when inserting all the column values into a table except the identity column you can exclude the column list. In fact if you try to exclude the column list from the following query that is inserting an identity column you get an error:
SET IDENTITY_INSERT Sailboat ON; INSERT INTO Sailboat VALUES(2,'Mumm', 'Mumm 30', '30 feet 0 inches','8 feet 2 inches', 37000.00); SET IDENTITY_INSERT Sailboat OFF;
You actually get the following error:
Msg 8101, Level 16, State 1, Line 16
An explicit value for the identity column in table 'Sailboat' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Naming only a Few Columns in the Column List
If you have columns that allow NULL values, or have default values assigned to the column you can exclude those columns from your column list. Here is an example of where I excluded the columns from my Sailboat table that were either an identity column, allowed nulls, or had a default value:
INSERT INTO Sailboat(Manufacture, Model) VALUES('Catalina', 'Catalina 42');
In this example I excluded the ID column from my column list because it was an identity column. I also excluded the Length and Beam columns since when I created my table I provided a default value of “Unknown” for these columns. Lastly I excluded the Price column from my selection list because the table definitions allowed null values for this column.
Inserting a Bunch of Rows into a Table
There are times when you might want to insert a bunch of rows into a table. One example of when you might want to do this is to populate a table with a series of test records. With the introduction of SQL Server 2008 Microsoft allowed you to provide multiple sets of values in a single INSERT statement. The code below demonstrates this:
INSERT INTO Sailboat (Manufacture, Model, Length, Beam, Price) VALUES ('Catalina', 'Catalina 26', '35 feet 11 inches','10 feet 2 inches', 42499.00), ('Santana', 'Santana 30', '30 feet 0 inches', '9 feet 9 inches',17000.00), ('Cal Marine', 'Cal 25 Mark I', '25 feet o inches', '8 feet 0 inches', 4585.00);
In this example I placed a comma after the first value list, then added a second value list followed by a comma, and then finally ended my list of values after the third set of values. When I ran the above single INSERT statement it inserted three different rows into my Sailboat table.
Using a SELECT Statement to Insert Data
There are times that you might need to insert a bunch of data into a table that comes from another table or a series of tables. It would be very hard for you to hand code a bunch of VALUES clauses to insert data to handle this situation. As luck would have it, you can provide data to an INSERT statement via a SELECT statement. In order to demonstrate this I first need to create a table that contains sailboat data using the following code:
CREATE TABLE SailboatDataToImport ( Manufacture varchar(50) NOT NULL, Model varchar(30) NOT NULL, Length varchar(20) NOT NULL, Beam varchar(20) NOT NULL, Price money NOT NULL); INSERT INTO SailboatDataToImport VALUES ('Pearson','27','27 feet 2 inches','8 feet',9000.00), ('Aquarius','23','23 feet','7 feet',3000.00), ('Vanguard','17','17 feet 3 inches','6 feet 4 inches',5000.00);
Here I have created a table named SailboatDataToImport that contains the records I want to import into my Sailboat table. I inserted three different sample records into this table.
It is very easy to use a SELECT statement to INSERT records into a table. The code in the following example will copy the records from the SailboatDataToImport table to the Sailboat table:
INSERT INTO Sailboat SELECT Manufacture, Model, Length, Beam, Price FROM SailboatDataToImport;
The above INSERT statement inserted my three sample records from the SailboatDataToImport table into my Sailboat table. It did this with a SELECT statement. As you can see instead of providing a VALUES clause in my INSERT statement I replaced it with a SELECT statement. That SELECT statement selected a column value from the SailboatDataToImport table for every column that I would normally place in a VALUES clause.
Using a Stored Procedure to Insert Data
There are times when you might want more complex logic to insert data into a table. When this occurs you can use a stored procedure to insert data into a table. Review the following code that inserts data into my Sailboat table using a stored procedure:
CREATE PROC NewBoats AS DECLARE @I int = 0; WHILE @I < 5 BEGIN SET @I = @I + 1; SELECT 'Larsen', 'Model ' + CAST(@I as char(1)), CAST(@I * 10 as CHAR(2)) + ' feet', CAST(6 + @I as CHAR(2)) + ' feet', CASE WHEN @I = 1 THEN @I * 1234 WHEN @I = 2 THEN @I * 2345 WHEN @I = 3 THEN @I * 5698 WHEN @I = 4 THEN @I * 11530 WHEN @I = 5 THEN @I * 42325 END; END GO INSERT INTO Sailboat EXEC NewBoats;
In this code I first created a stored procedure called “NewBoats”. This stored procedure produces a record set that contains five different sailboat records. Each record contains all of the columns needed in my Sailboat table. To insert the records produced by the “NewBoats” store procedure I use an INSERT statement that executes the stored procedure.
Using the OUTPUT Clause
There is an OUTPUT clause on an INSERT statement that allows the INSERT statement to output values for the columns in the rows inserted. These outputted values can then be used by a calling application or following on TSQL code. To demonstrate lets’ review the following code:
INSERT into Sailboat OUTPUT INSERTED.* VALUES ('Newport','Model 30','30 feet 2 inch','10 feet 3 inches','21000');
When this code is run I get the following results:
ID Manufacture Model Length Beam Price -- -------------- --------- ------------------- -------------------- --------------------- 53 Newport Model 30 30 feet 2 inch 10 feet 3 inches 21000.00
In my INSERT statement I added the additional parameter “OUTPUT INSERTED.*”. By adding this additional parameter to my INSERT statement I told the query engine to return the values that were inserted. Since I specified “INSERTED.*” I asked for the values of all columns from the inserted row to be returned. If I had just specified “INSERTED.ID” the query engine would have only returned the ID value. Using the OUTPUT clause to return the ID value might be useful if you want to store the ID value that is generated via the identity constraint in another table to maintain some relationship with the row you are inserting.
In order to use the results of the OUTPUT clause in your TSQL code you need to get it into a table or table variable. Below I have modified my code from above to place the results into a table variable:
DECLARE @INSERTED as TABLE ( ID int, Manufacture varchar(50), Model varchar(30), Length varchar(20), Beam varchar(20), Price money); INSERT into Sailboat OUTPUT INSERTED.* INTO @INSERTED VALUES ('Newport','Model 30','30 feet 2 inch','10 feet 3 inches','21000'); SELECT * FROM @INSERTED;
In this code my temporary table variable @INSERTED holds the values of the columns inserted.
In both of my prior two examples I used the notation INSERTED.* to identify the columns I wanted outputted. If you only desire to output a few column values you can do that by specifying the columns you want outputted. Here is an example of where I only outputted the identity column ID into my temporary table variable:
DECLARE @INSERTED as TABLE ( ID int); INSERT into Sailboat OUTPUT INSERTED.ID INTO @INSERTED VALUES ('Newport','Model 30','30 feet 2 inch','10 feet 3 inches','21000'); SELECT * FROM @INSERTED;
Add New Rows to a Table
When you need to add new rows to a table the INSERT statement is the TSQL command to accomplish that. With a single INSERT statement you can insert a single row or multiple rows. By using the OUTPUT clause you can return the values inserted into the target table, including the identity column value.
This article covered some of the INSERT options available, but not all. For a complete understanding of all the capabilities of the INSERT statement refer to Books Online.