SQL INSERT INTO Statement

INSERT INTO statement is used to add new rows to an existing table. It can insert values into all columns, specific columns, or even copy data from another table. This command is essential for populating databases with meaningful records such as customers, employees or students.

Let’s explore different ways to use the INSERT INTO statement:

1. Inserting Data into All Columns

This method is used when you want to insert data into all columns of a table without specifying column names. We simply provide the values for each column, in the same order that the columns are defined in the table.

Syntax:

INSERT INTO table_name 
VALUES (value1, value2, value3, ...); 

Parameters:

• table_name: name of the table where data will be inserted
• value1, value2... : values that correspond to each column in order.

Example: Let’s see how the INSERT INTO statement works with practical examples.

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, 'Liam', 'New York', 'xxxxxxxxxx', 18),
               (2, 'Sophia', 'Berlin', 'xxxxxxxxxx', 18),
               (3, 'Akira', 'Tokyo', 'xxxxxxxxxx', 20),
               (4, 'Carlos', 'Tokyo', 'xxxxxxxxxx', 18);

Output

If we don’t want to specify the column names (and you’re inserting data into all columns), we can directly insert values in the order they appear in the table structure. Here's an example:

Query:

INSERT INTO Student 
VALUES (5, 'Isabella', 'Rome', 'xxxxxxxxxx', 19);

Output

2. Inserting Data into Specific Columns

In some cases, you might want to insert data into only certain columns, leaving the others empty or with default values. In such cases, we can specify the column names explicitly.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...); 

Parameters:

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

Example: Let’s say we only want to insert the student's ID, name, and age into the Student table, and leave the address and phone number as NULL (the default value).

INSERT INTO Student (ROLL_NO, NAME, AGE) 
VALUES (6, 'Hiroshi', 19);

Output

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

3. Inserting Multiple Rows at Once

Instead of running multiple INSERT INTO commands, you can insert multiple rows into a table in a single query. This is more efficient and reduces the number of database operations.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), (value1, value2, ...);

Example: If we want to add multiple students to Student table in one go, query would look like this:

INSERT INTO Student (ROLL_NO, NAME, ADDRESS, PHONE, AGE) 
VALUES
(7, 'Mateo Garcia', 'Madrid', 'xxxxxxxxxx', 15),
(8, 'Hana Suzuki', 'Osaka', 'xxxxxxxxxx', 18),
(9, 'Oliver Jensen', 'Copenhagen', 'xxxxxxxxxx', 17),
(10, 'Amelia Brown', 'London', 'xxxxxxxxxx', 17);

Output

Explanation:

• Faster than multiple single inserts
• For very large data (>1000 rows), use bulk insert

4. Inserting Data from One Table into Another Table

We can also copy data from one table into another table using the INSERT INTO SELECT statement. This is very useful when we want to move or replicate data from one table to another without manually typing all the data.

Here, we are using below table OldStudent as another table and we will insert its rows into Student table using different methods.

Method 1: Insert All Columns from Another Table

Inserts every column from source table into destination table

INSERT INTO target_table
SELECT * FROM source_table;

Example: If you want to copy all data from the OldStudent table into the Student table, use this query:

INSERT INTO Student
SELECT * FROM OldStudent;

Output

Method 2: Insert Specific Columns from Another Table

Allows inserting only selected columns from the source table.

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table;

Example: Let’s say we want to copy only the Name and Age columns from OldStudent into Student:

INSERT INTO Student (Name, Age)
SELECT Name, Age
FROM OldStudent;

Output

Note: Columns not included in INSERT statement (ROLL_NO, ADDRESS, PHONE) are automatically filled with NULL in target table.

Method 3: Insert Specific Rows Based on Condition

You can also insert specific rows based on a condition by using the WHERE clause with the SELECT statement.

INSERT INTO target_table
SELECT * FROM source_table
WHERE condition;

Example: If we want to copy only students older than 20 years from OldStudent to Student, we would write:

INSERT INTO Student
SELECT * FROM OldStudent
WHERE Age > 20;

Output