SQL Insert Query: The Complete Guide

The query INSERT INTO can contain the values for some or all of its columns. INSERT INTO can be combined with a SELECT to insert records. If you are unfamiliar with creating a table in SQL, please check out my tutorial on how to create a table in SQL.

SQL Insert

SQL INSERT INTO statement is used to add new values to the Database. The INSERT INTO statement adds the new record to the table.

Syntax

The general syntax of SQL INSERT INTO Statement is following.

INSERT INTO table-name (column-names) 
VALUES (values)

Parameters

Here, column-names could be column1, column2, column3,…columnN are the names of the columns in a table into which you want to insert data into the Database.

You may not need to specify a column(s) name in the SQL query if you are adding the values for all the columns of a table. But make sure you need to preserve the order of the columns. Let’s take an example.

Step 1: Create a SQL Table.

Using MacOS and SQLite clients. You can use phpMyAdmin or Oracle, or other database software. Type the following query to create a table.

CREATE TABLE Apps (
    AppID int,
    AppName varchar(255),
    CreatorName varchar(255),
    AppCategory varchar(255),
    AppPrice int 
);

So, we have created a table called Apps which has five columns.

Step 2: Insert the values inside the table in SQL

Okay, the next step is to use the SQL INSERT INTO query to add the rows.

INSERT INTO Apps (AppID,`AppName`,`CreatorName`,`AppCategory`,`AppPrice`)
VALUES (1, 'AppDividend', 'Krunal', 'Software', 50 );

Now, run the query and see in the table that one row is added.

So, we have successfully inserted data into the Database.

Insert Multiple Values in Database

We can also insert multiple values into the table. See the following query.

INSERT INTO Apps (AppID,`AppName`,`CreatorName`,`AppCategory`,`AppPrice`)
VALUES 
(2, 'Escrow', 'LVVM', 'Fashion', 60 ),
(3, 'KGB', 'MJ', 'Music', 70 ),
(4, 'Moscow', 'Mayor', 'Area', 80 ),
(5, 'MoneyControl', 'Mukesh', 'Investment', 90 ),
(6, 'Investing', 'Bill', 'Stocks', 100 )

The output of the above SQL query is following.

If you are adding values for all of the columns of a table, you do not need to specify the column names in an SQL query.

However, ensure the order of the values is the same as the columns in a table. 

Populate one table using another table

You can populate the values into the table through the select statement over another table, provided that the other table has the fields required to fill the first table. For example, see the following SQL query. First, let’s try to add the data to the DummyApp table.

INSERT INTO DummyApp (AppID, `AppName`)
   SELECT AppID, AppName
   FROM Apps;

In the above query, we insert two column values inside the DummyApp table using the Apps table.

The DummyApp table has only two columns which are AppID and AppName.

Also, the Apps table has two columns. That is why we can easily add all the values from one table to another. For example, if you run the above query and set up the DummyApp table, all the values from the Apps table will be copied to the DummyApp table.

Insert The Data Only in Specified Columns

It is also possible to only insert data in the specific columns. See the following query.

INSERT INTO Apps (AppID, `AppName`)
VALUES (10, 'Stavanger');

So, the above query only inserts one row and two column values.

All the other column values should be NULL for that particular row.

Here, you need to be very careful because if some column does not allow the NULL values, then SQL Engine throws an exception, and the data will not be inserted into the Database.

That’s it for this tutorial.