SQL INSERT INTO Statement

The INSERT INTO statement is a fundamental SQL command used to add new rows of data into a table in a database. It is one of the most commonly used SQL statements for manipulating data and plays a key role in database management.

This article will explore the SQL INSERT INTO statement in detail, showing how to use it effectively with multiple examples. We’ll cover both basic and advanced usage, including inserting individual rows, multiple rows, and even copying data between tables.

SQL INSERT INTO Statement

• The INSERT INTO statement in SQL is used to add new rows of data to a table in a database.
• There are two main ways to use the INSERT INTO statement by specifying the columns and values explicitly or by inserting values for all columns without specifying them.

There are two primary syntaxes of INSERT INTO statements depending on the requirements. The two syntaxes are:

Syntax 1. Only Values

The first method is to specify only the value of data to be inserted without the column names.

INSERT INTO table_name

VALUES (value1, value2, value); 

Parameters:

• table_name: name of the table.
• value1, value2: value of first column, second column,… for the new record

Syntax 2. Column Names And Values Both

In the second method we will specify both the columns which we want to fill and their corresponding values as shown below:

INSERT INTO table_name (column1, column2, column3) 

VALUES ( value1, value2, value);

Parameters:

• table_name: name of the table. 
• column1, column2..: name of first column, second column.
• value1, value2, value..:  the values for each specified column of the new record.

Examples of SQL INSERT INTO

For better understanding, let’s look at the SQL Server INSERT statement with examples.

Let us first create a table named ‘Student‘.

CREATE DATABASE StudentDB;
USE StudentDB;

CREATE TABLE Student (
    ROLL_NO INT PRIMARY KEY,
    NAME VARCHAR(50),
    ADDRESS VARCHAR(100),
    PHONE VARCHAR(15),
    AGE INT
);

INSERT INTO Student (ROLL_NO, NAME, ADDRESS, PHONE, AGE) VALUES
(1, 'Ram', 'Delhi', 'XXXXXXXXXX', 18),
(2, 'Ramesh', 'Gurgaon', 'XXXXXXXXXX', 18),
(3, 'Sujit', 'Rohtak', 'XXXXXXXXXX', 20),
(4, 'Suresh', 'Rohtak', 'XXXXXXXXXX', 18);

Created Table:

Now, Suppose we want to add values. 

Example 1: Inserting Only New Values Using INSERT INTO Example

If we want to insert only values then we use the following query.

Query:

INSERT INTO Student 
VALUES ('5','HARSH','WEST BENGAL', 'XXXXXXXXXX','19');

Output: 

The table Student will now look like this:

Example 2: Insert Values to Specified Columns Using INSERT INTO Example

If we want to insert values in the specified columns then we use the following query.

Query:

INSERT INTO Student (ROLL_NO, NAME, Age) 
VALUES ('5','PRATIK','19');

Output:

The table Student will now look like this:

Note: Columns not included in the INSERT statement are filled with default values (typically NULL).

INSERT Multiple Rows in MS SQL Server

We can insert multiple rows into a table using a single INSERT INTO statement. This method saves time and reduces the potential for errors.

Syntax

INSERT INTO table_name(Column1,Column2,Column3,.......) 
VALUES 
        (Value1, Value2,Value3,.....),
        (Value1, Value2,Value3,.....),
        (Value1, Value2,Value3,.....),
         ............................. ;

Parameters:

• table_name: name of the table.
• Column 1: name of the first column, second column.
• Values: Value1, Value2, Value3: the value of the first column, second column.

Example of Insert Multiple Rows in a table using Single SQL Statement

The following SQL statement inserts multiple rows in Student Table.

Query:

INSERT INTO Student (ROLL_NO, NAME, AGE, ADDRESS, PHONE) 
VALUES
(6, 'Amit Kumar', 15, 'Delhi', 'XXXXXXXXXX'),
(7, 'Gauri Rao', 18, 'Bangalore', 'XXXXXXXXXX'),
(8, 'Manav Bhatt', 17, 'New Delhi', 'XXXXXXXXXX'),
(9, 'Riya Kapoor', 10, 'Udaipur', 'XXXXXXXXXX');

Output:

If a user wants to insert more than 1000 rows, multiple insert statements, bulk insert or derived table must be used. 

Using SQL INSERT INTO SELECT

The SQL INSERT INTO SELECT statement is used to copy data from one table and insert it into another table. The use of this statement is similar to that of the INSERT INTO statement.

The difference is that the SELECT statement is used here to select data from a different table. The different ways of using INSERT INTO SELECT statement are shown below:

Syntax of INSERT INTO SELECT

There are two syntaxes for using INSERT INTO SELECT statement, depending on it’s use.

1. Copy All Columns and Insert

The syntax for using INSERT INTO SELECT query to insert all data from a table to another table:

INSERT INTO first_table 
SELECT * 
FROM second_table;

Parameters:

•  first_table: name of first table. 
• second_table: name of second table.

We have used the SELECT statement to copy the data from one table and the INSERT INTO statement to insert from a different table.

2. Copy Specific Columns and Insert 

The syntax for using INSERT INTO SELECT query to insert specific data from a table to another table:

INSERT INTO first_table(names_of_columns1) 
SELECT names_of_columns2 
FROM second_table; 

Parameters:

• first_table: name of first table. second_table: name of second table.
• names of columns1: name of columns separated by comma(,) for table 1.
• names of columns2: name of columns separated by comma(,) for table 2.

We have used the SELECT statement to copy the data of the selected columns only from the second table and the INSERT INTO statement to insert in the first table.

3. Copy Specific Rows and Insert 

We can copy specific rows from a table to insert into another table by using the WHERE clause with the SELECT statement. We have to provide appropriate conditions in the WHERE clause to select specific rows.

The syntax for using INSERT INTO SELECT query to insert specific rows from table

INSERT INTO table1 
SELECT * FROM table2 
WHERE condition; 

Parameters:

• first_table: name of first table.
• second_table: name of second table. 
• condition: condition to select specific rows.

Examples of SQL INSERT INTO SELECT

Let’s look at some examples of INSERT INTO SELECT statement to understand it better.

Suppose there is a LateralStudent database.

Example 1: Inserting all rows and columns using INSERT INTO SELECT example

If we want to insert only values then we use the following query:

Query:

INSERT INTO Student 
SELECT * FROM LateralStudent;

Output:

This query will insert all the data of the table LateralStudent in the table Student. The table Student will now look like this,

Example 2: Inserting specific columns using INSERT INTO SELECT

If we want to insert values in the specified columns then we use the following query:

Query:

INSERT INTO Student(ROLL_NO,NAME,Age) 
SELECT ROLL_NO, NAME, Age FROM LateralStudent;

Output:

This query will insert the data in the columns ROLL_NO, NAME, and Age of the table LateralStudent in the table Student and the remaining columns in the Student table will be filled by null which is the default value of the remaining columns. The table Student will now look like this,

Example 3: Insert specific rows using INSERT INTO SELECT

INSERT INTO Student 
SELECT * FROM LateralStudent WHERE Age = 18;

Output:

This query will select only the first row from table LateralStudent to insert into the table Student. The table Student will now look like this,

Important Points About SQL INSERT INTO Statement

• The ‘INSERT INTO‘ statement is used to add new records to a table in a database
• It allows inserting multiple records in a single statement by providing multiple sets of values.
• If you don’t specify column names, the statement assumes all columns and the values must be in the same order as the table definition.
• Columns not included in the INSERT statement will be filled with default values, which are typically NULL.
• Using a single INSERT INTO statement for multiple rows improves efficiency and reduces potential errors.

Conclusion

Overall, INSERT INTO statement is an essential feature in SQL for adding new data to tables. Whether you’re adding a single row or multiple rows, specifying column names or copying data from another table, INSERT INTO provides the necessary flexibility. Understanding its syntax and usage is crucial for efficient database management and data manipulation.

FAQs

What is the INSERT INTO statement in SQL?

The INSERT INTO statement in SQL is used to add new rows of data to a table. It can either specify the columns to insert data into or insert data into all columns if they are provided in order. This statement is fundamental for data insertion operations in a database.

Which SQL statement is used to insert?

The INSERT INTO statement is the SQL command used to insert new rows into a table. It allows for the addition of new records by specifying the table name, column names (optional), and the corresponding values to be inserted.

What is the difference between `SELECT INTO` and `INSERT INTO` in SQL?

The SELECT INTO statement is used to create a new table and populate it with data from an existing table based on a SELECT query. In contrast, the INSERT INTO statement adds new rows to an existing table without creating a new table. INSERT INTO can insert specific values or copy data from another table, whereas SELECT INTO focuses on creating and populating a new table in one step.

H

Harsh Agarwal

Improve

Article Tags :

• Databases
• SQL
• SQL-Clauses-Operators