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):
- You can insert the values that you fixedly choose (INSERT INTO … VALUES (value1, value2, …))
- You can insert the values into the table using script (SELECT (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.
- If the target table into which you want to insert already exists, you will need to choose alternative INSERT INTO Table SELECT
All three scenarios are described below
SQL INSERT Syntax
(1) 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');
(2) Creating table and inserting values from another table into it (new table does not already exist):
SELECT (column 1, column 2, column 3,...columnN)
INTO dbo.New_Table
FROM dbo.Table;
Interesting fact: 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.
(3) 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 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.