SQL INSERT INTO Statement

We know that data is something that we are generating every single day. These new data need to be added to the tables in a database. In SQL, we have the INSERT INTO statement that allows us to add new records of data into a table and helps us perform manipulation, as it plays a crucial role in a DBMS (database management system).

In this tutorial, we are going to learn SQL INSERT INTO statement with its syntax and examples. Also, there are different scenarios, like inserting individual rows, multiple rows or adding another table as well.

Watch this Insert query in SQL video

Table of Contents

• What is SQL INSERT INTO Statement
• Examples of SQL INSERT INTO
• SQL INSERT INTO SELECT
• Examples of SQL INSERT INTO SELECT
• Conclusion

What is SQL INSERT INTO Statement?

The SQL INSERT statement is one of the fundamental commands that we use to manipulate and update the table by adding new rows of data to a table. For example, if you want to add new values to the columns, we will use the INSERT INTO statement by specifying the table name along with its columns and their respective values.

Syntax of INSERT INTO Statement

INSERT INTO table_name 

VALUES (value1, value2, value3, ...);

After writing the INSERT INTO command, we pass the table name followed by the values of individual columns, respectively.

Syntax for Inserting Multiple Rows

The syntax for inserting multiple rows is similar to that of a single row but includes multiple sets of values

INSERT INTO table_name (column1, column2, column3)

VALUES 

(value1a, value2a, value3a),

(value1b, value2b, value3b);

Examples of SQL INSERT INTO

Let’s take an example of a Student table where we are going to insert some rows with their values. We can add multiple rows by just passing records separated by a comma.

INSERT INTO Student (StudentName, City, Course)

VALUES 

('Alice', 'New York', 'Data Science with AI'),

('Bob', 'London', 'Database'),

('Charlie', 'Sydney', 'Core Python');

The above command will add three rows to a Student table.

SQL INSERT INTO SELECT

The INSERT INTO SELECT allows data to be copied from one table to another. This is useful because large amounts of data can always be transferred efficiently.

Syntax for INSERT INTO SELECT

Here, we have entered the target table after the INSERT INTO command and then passed the columns that you want to use. After SELECT, you will pass the column names from another table (the source table).

INSERT INTO target_table (column1, column2)

SELECT column1, column2 FROM source_table;

Examples of SQL INSERT INTO SELECT

Let’s think that you want to copy all the data from the ActiveCustomers table and add it to the AllCustomer table. 

ActiveCustomers: 

CREATE TABLE ActiveCustomers (

    CustomerID INT PRIMARY KEY AUTO_INCREMENT,

    CustomerName VARCHAR(50),

    Email VARCHAR(100)

);

AllCustomers:

CREATE TABLE AllCustomers (

    CustomerID INT PRIMARY KEY AUTO_INCREMENT,

    CustomerName VARCHAR(50),

    Email VARCHAR(100)

);

INSERT INTO ActiveCustomers (CustomerName, Email)

VALUES

    ('Rajesh Kumar', '[email protected]'),

    ('Aditi Sharma', '[email protected]'),

    ('Anil Mehta', '[email protected]'),

    ('Pooja Singh', '[email protected]'),

    ('Vikram Reddy', '[email protected]');

-- Insert data from ActiveCustomers into AllCustomers table

INSERT INTO AllCustomers (CustomerName, Email)

SELECT CustomerName, Email FROM ActiveCustomers;

SELECT * FROM AllCustomers;

It makes sure that you are maintaining the updated list of all customers without manually inputting each record.

Conclusion

Learning the concepts of the INSERT INTO statement in SQL gave us clarity about how we can manipulate and update the records of data in a table. We can add a single row or multiple rows for a specific column and also copy the data from one table to another. Mastering this concept will allow us to easily update the tables during database migrations or data preprocessing tasks.