Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.
 
SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.
 

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

SQL Server – INSERT INTO

23 June 2020

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:

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:

INSERT INTO Prods (ProductName_1, Price_1, Manufacturer_1)
VALUES ('iPhone 6S', 41000, 'Apple')

Here the value is specified for three columns only. And now the values are passed in the order of the columns:

  • ProductName: ‘iPhone 6S’.
  • Manufacturer: ‘Apple’
  • Price: 41000

For unspecified columns (in this case ProductCount) a default value will be added if the DEFAULT attribute or the NULL value is set. The unspecified columns must either be NULL or have the DEFAULT attribute.

We can also add more than one row at a time:

INSERT INTO Prods
VALUES
('iPhone 6', 'Apple', 3, 36000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000)

In this case, three rows will be added to the table.

Also when adding, we can specify to use the default value for the column using DEFAULT keyword or NULL value:

INSERT INTO Prods (ProductName_1, Manufacturer_1, ProductCount_1, Price_1)
VALUES ('Mi6', 'Xiaomi', DEFAULT, 28000)

In this case, the default value for the ProductCount column will be used (if it is set, if it is not, then NULL).

If all columns have the DEFAULT attribute that defines the default value, or allow NULL, you can insert the default values for all columns:

INSERT INTO Prods
DEFAULT VALUES

But if you take the Products table, this command will end with an error, because several fields do not have the attribute DEFAULT and do not allow the value NULL.

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

 
Tags: , , , , , ,

MORE NEWS

 

Preamble​​NoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...

Preamble​​MongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...

5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...

The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...

Preamble​​MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...

First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...

Preamble​​Atom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...

Preamble​​MongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...

FlexibilityOne of the most advertised features of MongoDB is its flexibility.  Flexibility, however, is a double-edged sword. More flexibility means more...

Preamble​​SQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...

Preamble​​Writing an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...

Preamble​​Oracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...

Preamble​​IBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...

  What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....

Preamble​​If you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...

Preamble​​Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...