SQL Command INSERT is suitable for situations when we want to insert entries into the table. Insertion of rows can be done in multiple ways (further description below):

  1. You can insert the values that you choose (INSERT INTO … VALUES (value1, value2, …))
  2. You can insert the values into the table using script (SELECT clause – i.e. selecting values from the table) in combination with INTO “target table”). In this process, you basically combine creating table, i.e. CREATE TABLE and insertion of values defined in SELECT clause. In this case, the target table does not exist.
  3. If the target table into which you want to insert already exists, you will need to choose alternative INSERT INTO Table SELECT

SQL INSERT Command Syntax

There are 3 basically 3 options. All three scenarios are described below

Basic syntax in case when you want to insert one or few values

INSERT INTO dbo.Table (column 1, column 2, column 3,...columnN 
VALUES ('value 1', 'value 2', 'value 3',...'value N');

Creating table and inserting values from another table into it (new table does not already exist)

Using this method will create table without any object that is bound to original table – primary keys, indexes, statistics, triggers etc. New table contains only data and definition of the table as such corresponds to the original table.

SELECT (column 1, column 2, column 3,...columnN)  
INTO dbo.New_Table
FROM dbo.Table;

Inserting values into table as result of a query

INSERT INTO dbo.Table (column 1, column 2, column 3,...columnN)
SELECT (column 1, column 2, column 3,...column N)
FROM dbo.Table;

Common Mistakes when using INSERT

When inserting values into the table using command INSERT INTO following must apply:

  • Number and name of the columns of the table into which you insert values must match with source values. If not, operation will result in error: “The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns” Fig.1

The select list for the INSERT statement contains fewer

  • Data types must match with each other. E.g. you cannot insert VARCHAR into column with INTEGER data type. Operation will probably result in some kind of conversion error as “Conversion failed when converting the varchar value ‘1.1’ to data type int” Fig.2

Conversion failed when converting the varchar value

More about this command is accessible through Microsoft technical documentation here.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *