SQL INSERT INTO Statement

The SQL INSERT INTO statement is one of the most essential commands for adding new data into a database table. Whether you are working with customer records, product details or user information, understanding and mastering this command is important for effective database management.

How SQL INSERT INTO Command Works

The SQL INSERT INTO statement allows you to add new rows of data into an existing table in a database. It’s a fundamental command in SQL, frequently used to populate tables with data. There are two primary methods for using this command:

1. Inserting Data into All Columns: If you want to insert data into all columns of the table, you can omit the column names and simply provide values for every column.
2. Inserting Data with Specific Columns: You can explicitly specify the columns you want to insert data into, followed by the corresponding values.

The choice of method depends on whether you're inserting data for all the columns in a table or only for a subset of them.

1. Inserting Data into All Columns (Simple Method)

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, value); 

Parameters:

• table_name: The name of the table where the data will be inserted
• value1, value2: The values you want to insert into the respective columns of the table

Example:

For better understanding, let's look at the SQL INSERT INTO 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);

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','HARSH','WEST BENGAL', 'XXXXXXXXXX','19');

Output

2. Inserting Data into Specific Columns (Flexible Method)

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, 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.

Example :

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

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

Output:

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

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 the Students table in one go, the query would look like this:

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:

Explanation:

• This method is faster than running multiple individual INSERT INTO commands.
• If you're inserting more than 1000 rows, consider using bulk insert or multiple insert statements for efficiency.

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.

Syntax 1: Insert All Columns from Another Table

INSERT INTO target_table

SELECT * FROM source_table;

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

INSERT INTO Students
SELECT * FROM OldStudents;

Syntax 2: Insert Specific Columns from Another 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 OldStudents into Students:

INSERT INTO Students (Name, Age)
SELECT Name, Age
FROM OldStudents;

Syntax 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 OldStudents to Students, we would write:

INSERT INTO Students
SELECT * FROM OldStudents
WHERE Age > 20;

Important Points About SQL INSERT INTO Statement

• Multiple Inserts: You can insert multiple rows at once by separating each set of values with commas. This reduces the number of queries you need to run.
• NULL Values: If you don't insert data into a column, it will typically be set to NULL unless the column has a default value.
• Order of Columns: When using the simple INSERT INTO syntax without specifying column names, the values must be in the exact same order as the columns are defined in the table.
• Default Values: Columns not mentioned in the INSERT INTO statement will be filled with their default values (often NULL).
• Efficiency: Inserting multiple rows at once is much more efficient than doing it one by one.

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.