SQL Server INSERT INTO

Preamble

A T-SQL command called INSERT INTO is used to add data to a table, or to add new records. Both mass inserting data and adding a single line to the table can be done using this instruction. Permission to insert data (INSERT) into the target table is necessary in order to execute the INSERT instruction.

INSERT INTO instruction in T-SQL

If you are at least somewhat familiar with the T-SQL language, you probably understood that this article will discuss how to add data to a table in Microsoft SQL Server. Now, we will discuss the INSERT instruction and how it can be used to do so.

The INSERT command is used to add data, and it has the formal syntax shown below:

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

The expression INSERT INTO is presented first, followed by a list of the columns to which data should be added in brackets, and finally, after the word VALUES, a list of the values added to the columns in brackets.

Let’s say that the database shown below was 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)

The INSERT instruction can be used in a variety of ways to insert data:

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

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 instance, the first column in the CREATE TABLE expression above is Id. But since it has the IDENTITY attribute set, the value of this column is generated automatically, so it is not necessary to set it. The first value, the line “iPhone 7,” will be passed to the second column, which represents ProductName. The third “Manufacturer” column will receive the second value, or the “Apple” line, and so forth. Thus, the values are sent to the columns in the manner described below:

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

You can also choose which columns the values will be added to when you enter them:

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.

In most cases, this is how new entries to tables are added; this is roughly how the INSERT INTO instruction’s simplified syntax looks.

The order of the columns in the table will be used to determine the order of the columns in the list if there isn’t a written list of the columns to which you will be adding data. Remember this order when you write a selection request or list the values to be put in. I advise you to specify a list of the columns to which you intend to add data.

Additionally, keep in mind that the so-called mandatory columns—those that cannot contain the value of NULL—must be present in both the list of columns and the list of values, respectively. If you omit them and the column has no default value, an error will occur.

Additionally, we would like to point out that the type of values you intend to insert should support implicit conversion or, at the very least, correspond to the type of column data in which they will be inserted. However, I suggest that you manage the value data type (format) in the SELECT query as well as the value list.

Enough theory; let’s get to work.

Input data

We need the table itself in order to add data to it, so let’s create it and start adding records to it.

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

A list of items with prices will be on our test table.

We will also use a procedure that returns a table value in the examples to add data to the table, so let’s create that as well.

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

For instance, it will deliver information from a brand-new TestTable table.

Example 1: Using the tabular value constructor, add a new row to the table.
Write a request for selection before adding more entries and seeing the results right away.

INSERT INTO TestTable_1(ProductName_1, Price_1) VALUES ('Computer', 100)  GO  SELECT * FROM TestTable_1

As you can see, we have listed the column names to which we will add data after the name of the table, followed by the keyword VALUES and the values we wish to insert in parentheses in the same order.

I wrote the SELECT instruction and used the GO command to separate it from the INSERT instruction.

Let’s now act as though we need to add a few more lines. For that, we will draft the following request.

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

In this instance, we added three lines, or entries. The values of each new line are enclosed in parentheses and separated by commas after VALUES.

Example 2: Using the SELECT query, add new rows to the table.
It is frequently necessary to add a lot of data to the table, for instance, based on a SELECT query that performs a sampling. Instead of using VALUES, we just need to specify a query to accomplish this.

 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 demonstration, we created a SELECT query that, when executed, returns data from the TestTable table, but only those records that have an identifier greater than 2, and we pasted the results into the same TestTable table.

Let’s create another insertion query that will accomplish the same task as the one above but will not list the columns to insert as an illustration of how you can add records to a table without specifying a list of columns.

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

In this instance, we can afford to write it this way because we are confident that ProductName and Price are the first and second columns, respectively, in the TestTable table. Again, though, it is usually preferable to specify a list of columns.

You may have noticed that I did not specify an Id column in any of the examples, but even without one there was no error because the IDENTITY property of this column automatically creates identifiers, making it impossible to insert data into it.

Example 3: Using the stored procedure, add new records to the table
Let’s now paste the information that will take us back to the stored procedure into the table. Here, a procedure call is specified in place of VALUES, but the meaning is the same. But as you might expect, even if no column list is specified, the order and number of columns returned by the procedure must exactly match the list of columns to be inserted.

 INSERT INTO TestTable_1(ProductName_1, Price_1) EXEC TestProcedure  GO  SELECT * FROM TestTable_1

You should now be able to understand the INSERT INTO manual, I hope.

SQL Tutorial: Inserting Data Into Tables

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.