SQL

INSERT in SQL

➕ The INSERT statement in SQL is used to add new records (rows) into a database table. It is one of the most commonly used Data Manipulation Language (DML)statements and allows you to insert a single row, multiple rows, or even the result of another query into a table.

📖 What is the INSERT Statement?

The INSERT statement creates new records by supplying values for one or more columns in a table. If required columns are omitted, the database uses their default values (if defined) or returns an error if the columns do not allow NULL values.

Information

An INSERT statement adds new data without affecting existing records.

🎯 Why Use INSERT?

The INSERT statement is essential for storing new information in a database.

📌 Add new customer, employee, or student records.
📌 Populate newly created tables.
📌 Import data from other tables.
📌 Insert multiple records efficiently.
📌 Store application-generated data.

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	Age	Marks
101	Alice	Computer Science	20	92
102	Bob	Mathematics	21	85

📝 Basic Syntax

INSERT Syntax

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

💡 Insert a Single Row

Insert one new student into the Students table.

Insert One Record

INSERT INTO Students
(StudentID, Name, Department, Age, Marks)
VALUES
(103, 'Charlie', 'Physics', 22, 78);

📊 Table After Insert

StudentID	Name	Department	Age	Marks
101	Alice	Computer Science	20	92
102	Bob	Mathematics	21	85
103	Charlie	Physics	22	78

📥 Insert Without Specifying Column Names

If values are supplied for every column in the correct order, the column list can be omitted.

Insert All Columns

INSERT INTO Students
VALUES
(104, 'David', 'Computer Science', 20, 95);

Important

Omitting the column list is only recommended when you provide values for every column in the exact order defined by the table.

📚 Insert Multiple Rows

Many SQL databases allow multiple rows to be inserted using a single INSERT statement.

Insert Multiple Records

INSERT INTO Students
(StudentID, Name, Department, Age, Marks)
VALUES
(105, 'Emma', 'Biology', 21, 88),
(106, 'Frank', 'Chemistry', 22, 81),
(107, 'Grace', 'Mathematics', 20, 91);

📋 Insert Selected Columns

You can insert values into only selected columns. The remaining columns use their default values or NULL, depending on the table definition.

Insert Specific Columns

INSERT INTO Students
(StudentID, Name, Department)
VALUES
(108, 'Henry', 'Physics');

🔄 INSERT INTO ... SELECT

Use INSERT INTO ... SELECT to copy data from one table into another.

Insert Data from Another Table

INSERT INTO GraduateStudents
(StudentID, Name, Department)
SELECT StudentID,
       Name,
       Department
FROM Students
WHERE Marks >= 90;

📊 INSERT with Default Values

If a column has a default value, you can omit it or explicitly use the DEFAULT keyword (where supported by your database).

Insert Using DEFAULT

INSERT INTO Students
(StudentID, Name, Department, Age, Marks)
VALUES
(109, 'Ivy', 'Physics', DEFAULT, 84);

⚖️ INSERT vs UPDATE

Feature	INSERT	UPDATE
Purpose	Adds new rows.	Modifies existing rows.
Affects Existing Data	❌ No	✅ Yes
Creates New Records	✅ Yes	❌ No

💼 Real-World Example

A university admission system needs to add newly enrolled students to the database every day.

Add a New Student

INSERT INTO Students
(StudentID, Name, Department, Age, Marks)
VALUES
(110, 'Jack', 'Computer Science', 19, 89);

Each successful execution adds a new student record to the database.

⚠️ Common Mistakes

❌ Providing values in the wrong column order.
❌ Supplying more or fewer values than required.
❌ Inserting duplicate values into a primary key column.
❌ Omitting required columns that do not allow NULL values and have no default value.

Warning

Always verify the table structure before inserting data. Primary keys must be unique, and data types should match the corresponding column definitions.

⚠️ Best Practices

Best Practice

Always specify column names in INSERT statements, validate data before inserting it, use multi-row inserts for better performance, rely on default values where appropriate, and use transactions when inserting related data into multiple tables.

🚀 Key Points to Remember

📌 INSERT adds new rows to a table.
📌 You can insert one row or multiple rows at once.
📌 Specifying column names is recommended.
📌 INSERT INTO ... SELECT copies data from another table.
📌 Default values are used when applicable.
📌 INSERT creates new records, while UPDATE modifies existing ones.

>>"The INSERT statement is the gateway for adding new information to a relational database."

Summary

✅ The INSERT statement is one of the fundamental SQL commands for adding data to database tables. Whether inserting a single row, multiple rows, or data from another table, mastering INSERT is essential for building reliable and efficient database applications.