SQL Server – INSERT INTO

INSERT INTO –  is a T-SQL instruction designed to add data to a table, i.e. create new records. This instruction can be used both for adding one line to the table and for mass inserting data. Executing the INSERT instruction requires permission to insert data (INSERT) to the target table.

INSERT INTO instruction in T-SQL

In this article we will talk about how you can add data to a table in Microsoft SQL Server, if you are at least a little familiar with the T-SQL language, you probably understood that now we will talk about the INSERT instruction and how it can be used to add data to a table.

To add data, the INSERT command is used, which has the following formal syntax:

INSERT [INTO] tab_name [(column_list)] VALUES (value_1, value_2, ... value_N)

First comes the expression INSERT INTO, then in brackets you can specify the list of columns in which to add data, and at the end after the word VALUES brackets list the values added to the columns.

For example, let the following database be created earlier:

CREATE DATABASE productsdb_1;
GO
USE productsdb_1;
CREATE TAB Prods
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName_1 NVARCHAR(30) NOT NULL,
    Manufacturer_1 NVARCHAR(20) NOT NULL,
    ProductCount_1 INT DEFAULT 0,
    Price_1 MONEY NOT NULL
)

Let’s add one line to it using the INSERT command:

INSERT Prods VALUES ('iPhone 7', 'Apple', 5, 52000)

There are several ways to use the INSERT instruction for the data to be inserted:

• Listing the specific values to be pasted;
• Specifies the data set as a SELECT query;
• Specifies the data set as a call to a procedure that returns tabular data.

After successful execution in SQL Server Management Studio the message “1 row(s) affected” should appear in the message field:

Note that the values for the columns in brackets after the VALUES keyword are passed in the order they are declared. For example, in the CREATE TABLE expression above you can see that the first column is Id. But since the IDENTITY attribute is set for it, the value of this column is automatically generated, and it is not necessary to specify it. The second column represents ProductName, so the first value – the line “iPhone 7” will be passed to this column. The second value – the “Apple” line will be passed to the third “Manufacturer” column and so on. That is, the values are passed to the columns as follows:

• ProductName: ‘iPhone 7’.
• Manufacturer: ‘Apple’
• ProductCount: 5
• Price: 52000

When entering values, you can also specify direct columns to which values will be added:

Simplified syntax

INSERT [INTO] [tab] (col list, ...)
    VALUES (list of values, ...)
    Or
    SELECT selection request
    Or
    EXECUTE procedure

Where,

• INSERT INTO is a command for adding data to a table;
• A table is the name of the target table in which you want to insert new records;
• Column list is a list of column names for the table in which the comma-separated data will be inserted;
• VALUES is a table value constructor with which we specify the values to be inserted into the table;
• Values list is the values to be inserted, separated by commas. They are listed in the order in which the columns in the column list are specified;
• SELECT is a query to select the data to be inserted into the table. The resulting data set that will return the query must match the column list;
• EXECUTE is a procedure call to obtain data for inserting into a table. The resulting set of data which will return the stored procedure must match the list of columns.

This is roughly what the simplified syntax of the INSERT INTO instruction looks like, in most cases this is how you will add new entries to the tables.

The list of columns in which you will be adding data may not be written, in which case their order will be determined based on the actual order of the columns in the table. Remember this order when you specify the values to be inserted or write a selection request. Personally, I recommend that you specify a list of columns to which you plan to add data.

You should also remember that in the list of columns and in the list of values, respectively, must be present so-called mandatory columns, these are those that can not contain the value of NULL. If you do not specify them, and the column has no default value, there will be an error.

We would also like to note that the type of values you are going to insert should correspond to the type of column data in which this value will be inserted, or at least it supports implicit conversion. But I advise you to control the value data type (format) in both the value list and the SELECT query.

Enough theory, let’s move on to practice.

Input data

In order to add data to the table, we need the table itself, let’s create it, and already try to add records to it.

Note! All examples will be executed in Microsoft SQL Server 2016 Express.

CREATE TABLE TestTable_1(
        [Id] [INT] IDENTITY(1,1) NOT NULL,
        [ProductName_1] [VARCHAR](100) NOT NULL,
        [Price_1] [Money_1] NOT NULL
   )

Our test table, will contain a list of products with price.

Also in the examples, we will use a procedure that returns a table value to add data to the table, so let’s create it too.

CREATE PROCEDURE TestProcedure_1
   AS
   BEGIN
        SELECT ProductName_1, Price_1
        FROM TestTable_1
   END

For example, it will return data from a newly created TestTable table.

Example 1 – Add a new entry to the table using the tabular value constructor
First let’s try to add one entry and look at the result right away, i.e. write a request for selection.

INSERT INTO TestTable_1(ProductName_1, Price_1)
           VALUES ('Computer', 100)

   GO

   SELECT * FROM TestTable_1

You can see that we have listed the column names to which we are going to add data after the table name, then we have specified the keyword VALUES and in parentheses we have also written the values we want to insert in the same order.

After the INSERT instruction, I wrote the SELECT instruction and separated it with the GO command.

Now let’s pretend that we need to add a few lines. We will write the following request for that.

  INSERT INTO TestTable_1(ProductName_1, Price_1)
           VALUES ('Computer', 100),
                   ('Keyboard', 20),
                   ('Monitor', 50)
   GO

   SELECT * FROM TestTable_1

In this case, we added three entries, i.e. three lines. After VALUES, the values of each new line are in parentheses, we separated them with commas.

Example 2 – Add new rows to the table using the SELECT query
Very often it is necessary to add a lot of data to the table, for example, on the basis of a sampling query, i.e. SELECT. To do this, we only need to specify a query instead of VALUES.

   INSERT INTO TestTable_1(ProductName_1, Price_1)
        SELECT ProductName_1, Price_1 
        FROM TestTable_1
        WHERE Id > 2
   GO

   SELECT * FROM TestTable_1

In this example, we wrote a SELECT query that returns data from the TestTable table, but not all of it, but only those with an identifier greater than 2, and we pasted the result into the same TestTable table.

As an example of how you can add records to a table without specifying a column list, let’s write another insertion query that will do the same thing as the above query, only it will not list the columns to insert.

INSERT INTO TestTable_1
           SELECT ProductName_1, Price_1
           FROM TestTable_1
           WHERE Id > 2
   GO

   SELECT * FROM TestTable_1

In this case, we are sure that the first column in the TestTable table is ProductName and the second is Price, so we can afford to write it this way. But, again, in practice it is better to specify a list of columns.

In case you noticed, I didn’t specify an Id column in all of the examples, and we have it, there was no error, because this column with the IDENTITY property, it automatically generates identifiers, so you simply can’t insert data into such a column.

Example 3 – Add new records to the table using the stored procedure
Now, let’s paste into the table the data that will return us to the stored procedure. The meaning here is the same, and instead of VALUES we specify a procedure call. But as you can imagine, the order and number of columns returned by the procedure must strictly match the list of columns to be inserted (even if no column list is specified).

   INSERT INTO TestTable_1(ProductName_1, Price_1)
           EXEC TestProcedure

   GO

   SELECT * FROM TestTable_1

I hope this material has helped you to understand the INSERT INTO manual.

SQL Tutorial: Inserting Data Into Tables