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

Last modified date:

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 fixedly choose (INSERT INTO … VALUES (value1, value2, …))
  2. 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.
  3. 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

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.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Handling SQL tables SQL Commands

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.