SQL INSERT INTO Statement

The SQL INSERT INTO statement is one of the most commonly used commands for adding new data into a table in a database. Whether you're working with customer data, products, or user details, mastering this command is crucial for efficient database management. Let’s break down how this command works, how to use it, and some advanced techniques to boost your productivity when inserting data.

What is SQL INSERT INTO Statement?

The SQL INSERT INTO statement is used to add new rows of data to a table in a database. It's one of the core commands in SQL and is commonly used to populate tables with data. 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. The method you choose depends on whether you want to insert data into every column or just a subset of columns in the table.

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.