SQL INSERT – Inserting Rows into Database Table (3 methods) + Common mistakes

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

• 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

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