INSERT INTO in SQL

A database becomes useful only when it can store information. After creating a table, the next step is usually to add records to it. SQL provides the INSERT INTO statement for adding new rows to an existing table.

INSERT INTO is a Data Manipulation Language (DML) statement. It is commonly used in applications such as student management systems, banking software, hospital systems, e-commerce websites, employee databases, and many other systems where new information needs to be stored.

In this tutorial, you will learn how INSERT INTO works, how to insert one or multiple records, how to handle NULL and default values, how to copy records using INSERT INTO SELECT, common insertion errors, and important interview questions.

What is INSERT INTO in SQL?

INSERT INTO is an SQL statement used to add one or more new rows to an existing database table.

For example, suppose a table named Student contains information about students. When a new student joins the college, an INSERT statement can be used to store that student's details.

INSERT INTO Student
(StudentID, StudentName, Course)
VALUES
(101, 'Rahul', 'BCA');

The statement creates a new record in the Student table.

Why is INSERT INTO Used?

INSERT INTO is required whenever new information needs to be stored in a database.

Adding a new student.
Registering a new customer.
Creating an employee record.
Storing a new product.
Recording a customer order.
Adding a hospital patient.
Saving transaction information.

In a real application, an INSERT operation may happen every time a user submits a registration form, places an order, creates an account, or enters new information.

INSERT INTO Syntax

The recommended form of the INSERT statement specifies the column names explicitly.

INSERT INTO TableName
(Column1, Column2, Column3)
VALUES
(Value1, Value2, Value3);

For example:

INSERT INTO Student
(StudentID, StudentName, Course)
VALUES
(101, 'Rahul', 'BCA');

Understanding the Syntax

Part	Meaning
INSERT INTO	Specifies that a new record will be added.
Student	Name of the target table.
StudentID, StudentName, Course	Columns that will receive values.
VALUES	Introduces the values being inserted.
101, Rahul, BCA	Actual data stored in the selected columns.

Creating a Table for INSERT Examples

Before inserting records, the destination table must already exist.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    Course VARCHAR(50),
    Age INT
);

Now the Student table is ready to receive records.

How to Insert a Single Row

The simplest form of INSERT INTO adds one record to a table.

INSERT INTO Student
(StudentID, StudentName, Course, Age)
VALUES
(101, 'Rahul', 'BCA', 20);

After execution, the table contains the following record:

StudentID	StudentName	Course	Age
101	Rahul	BCA	20

How to Insert Multiple Rows

When several records need to be added, multiple rows can be supplied in the same INSERT statement.

INSERT INTO Student
(StudentID, StudentName, Course, Age)
VALUES
(102, 'Priya', 'B.Tech', 21),
(103, 'Amit', 'MCA', 22),
(104, 'Neha', 'B.Sc', 19);

Three records are inserted by one SQL statement.

Why Use Multiple-Row INSERT?

Fewer SQL statements are required.
It can reduce communication between an application and database server.
It is convenient when several known records are added together.
It is generally more efficient than sending many individual statements.

Checking Inserted Records

The SELECT statement can be used to verify the data after insertion.

SELECT * FROM Student;

Example result:

StudentID	StudentName	Course	Age
101	Rahul	BCA	20
102	Priya	B.Tech	21
103	Amit	MCA	22
104	Neha	B.Sc	19

INSERT INTO Without Column Names

SQL also allows values to be inserted without explicitly writing column names.

INSERT INTO Student
VALUES
(105, 'Karan', 'BCA', 20);

In this form, the values must follow the table's column order.

Although this syntax is valid in many SQL systems, explicitly listing the columns is usually better because it makes the statement clearer and less dependent on the current column order.

Why Should Column Names Be Specified?

Using column names makes an INSERT statement easier to understand and maintain.

Clearly shows where each value belongs.
Allows insertion into selected columns.
Reduces mistakes caused by column ordering.
Makes SQL statements easier to review.
Works better when tables contain many columns.

Recommended approach:

INSERT INTO Student
(StudentID, StudentName, Course)
VALUES
(106, 'Riya', 'BCA');

Inserting Data into Selected Columns

You do not always need to provide a value for every column. If omitted columns allow NULL or have a default value, they can be left out.

INSERT INTO Student
(StudentID, StudentName)
VALUES
(107, 'Ankit');

Here, Course and Age are not supplied. Their final values depend on the table definition. If the columns allow NULL and have no default, NULL may be stored.

Inserting NULL Values

NULL represents the absence of a known value. It is different from zero, an empty string, or the word "NULL".

INSERT INTO Student
(StudentID, StudentName, Course, Age)
VALUES
(108, 'Pooja', NULL, NULL);

This example indicates that the course and age are currently unavailable.

A column defined with NOT NULL cannot accept NULL through a normal INSERT operation.

Inserting Text and Numeric Values

Text values are normally written inside single quotation marks, while numeric values are generally written without quotes.

INSERT INTO Product
(ProductID, ProductName, Price)
VALUES
(1, 'Laptop', 55000);

Here:

1 is a numeric value.
Laptop is text.
55000 is a numeric value.

Inserting Date Values

Date syntax can vary between database systems, so the format recommended by the particular DBMS should be followed.

A commonly used ISO-style date representation is:

INSERT INTO Student
(StudentID, StudentName, AdmissionDate)
VALUES
(109, 'Pooja', '2026-07-21');

The database stores the supplied date according to the definition of the AdmissionDate column.

Using DEFAULT Values with INSERT

A column can have a predefined DEFAULT value. When that column is omitted from an INSERT statement, the database can use its default value.

CREATE TABLE Employee (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Country VARCHAR(50) DEFAULT 'India'
);

Now insert an employee without specifying Country:

INSERT INTO Employee
(EmployeeID, EmployeeName)
VALUES
(1, 'Aman');

The Country column receives its defined default value.

INSERT with AUTO_INCREMENT or IDENTITY Columns

Many database systems provide automatically generated key values. The exact syntax differs between DBMS products.

For example, in MySQL:

CREATE TABLE Student (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(100)
);

A record can then be inserted without supplying StudentID:

INSERT INTO Student
(StudentName)
VALUES
('Rahul');

The database generates the identifier automatically.

Other systems may use features such as IDENTITY or sequences instead, so auto-generated key behavior should always be checked for the specific DBMS being used.

INSERT INTO SELECT

INSERT INTO is not limited to manually supplied VALUES. Data can also be copied from one table to another using INSERT INTO SELECT.

Syntax

INSERT INTO TargetTable
(Column1, Column2)
SELECT Column1, Column2
FROM SourceTable;

Example

INSERT INTO StudentBackup
(StudentID, StudentName)
SELECT StudentID, StudentName
FROM Student;

The selected records from Student are inserted into StudentBackup.

INSERT INTO SELECT with WHERE

A WHERE condition can be used with the SELECT part to copy only the required records.

INSERT INTO TopStudents
(StudentID, StudentName)
SELECT StudentID, StudentName
FROM Student
WHERE Marks > 80;

Only students satisfying the condition are copied.

This technique is useful for data migration, reporting tables, archiving, and transferring selected records between tables.

How Constraints Affect INSERT

Database constraints help prevent invalid data from entering a table. An INSERT statement must satisfy the applicable constraints.

PRIMARY KEY

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

Two rows cannot normally have the same primary key value.

INSERT INTO Student
VALUES (1, 'Rahul');

INSERT INTO Student
VALUES (1, 'Amit');

The second INSERT violates the primary key rule because the value 1 already exists.

NOT NULL

CREATE TABLE Employee (
    EmployeeID INT,
    EmployeeName VARCHAR(100) NOT NULL
);

EmployeeName must receive a value when a row is inserted unless the database definition provides another valid mechanism.

UNIQUE

CREATE TABLE Users (
    UserID INT,
    Email VARCHAR(100) UNIQUE
);

A duplicate value may be rejected when it violates the UNIQUE constraint, subject to the DBMS's NULL and constraint behavior.

CHECK

CREATE TABLE Student (
    StudentID INT,
    Age INT CHECK (Age >= 18)
);

An INSERT containing an age that violates the CHECK condition can be rejected by the database.

Common INSERT INTO Errors

An INSERT statement can fail for many reasons. Some of the most common problems are:

Incorrect number of values.
Incorrect data type.
Duplicate primary key.
Violation of UNIQUE constraint.
NULL supplied to a NOT NULL column.
Value violates a CHECK constraint.
Invalid foreign key value.
Incorrect date or timestamp format.

Example: Data Type Problem

INSERT INTO Student
(StudentID, StudentName)
VALUES
('ABC', 123);

If StudentID is numeric and StudentName is character data, the values do not match the intended column types and the DBMS may reject the statement or perform conversion according to its rules.

Foreign Key and INSERT

A foreign key ensures that a value in one table refers to an appropriate record in another table.

For example, suppose Employee contains DepartmentID as a foreign key referencing Department.

INSERT INTO Employee
(EmployeeID, EmployeeName, DepartmentID)
VALUES
(101, 'Amit', 50);

If DepartmentID 50 does not exist in the referenced Department table, the INSERT may fail because the foreign key relationship would be violated.

Real-World Example: Student Management System

When a student completes admission registration, the application can store the student's details in the database.

INSERT INTO Student
(StudentID, StudentName, Course)
VALUES
(501, 'Rahul Sharma', 'B.Tech');

The new student is now available for later operations such as attendance, examination, fee management, and reporting.

Real-World Example: E-Commerce System

When a customer places an order, the application may create a new record in an Orders table.

INSERT INTO Orders
(OrderID, CustomerName, Amount)
VALUES
(9001, 'Vikas', 2500);

The stored order can then be processed by other parts of the application.

Real-World Example: Hospital System

A hospital registration system can insert a new patient record when the patient is registered.

INSERT INTO Patient
(PatientID, PatientName, Disease)
VALUES
(3001, 'Anjali', 'Fever');

The record can subsequently be associated with appointments, prescriptions, billing, and other hospital operations.

INSERT INTO vs UPDATE

INSERT and UPDATE are both DML statements, but they perform different operations.

Feature	INSERT	UPDATE
Purpose	Adds new rows	Changes existing rows
Creates a new record	Yes	No
Can use WHERE	Not with VALUES syntax	Yes
Typical use	Store new information	Modify existing information

INSERT INTO vs INSERT INTO SELECT

Feature	INSERT ... VALUES	INSERT ... SELECT
Source of data	Explicit values	Result of a SELECT query
Useful for	New records	Copying or transferring records
Can use WHERE	No	Yes, through SELECT
Example	VALUES (...)	SELECT ... FROM ...

Best Practices for INSERT INTO

Specify column names explicitly.
Validate data before inserting it.
Use appropriate data types.
Define suitable constraints.
Use transactions for important multi-step operations.
Use parameterized queries in application code.
Use multi-row insertion when appropriate.
Check foreign key relationships.
Handle duplicate and validation errors properly.

Important Point: SQL Injection and INSERT

When INSERT statements are generated from application input, user-provided values should not be directly concatenated into SQL strings.

Applications should use parameterized queries or prepared statements. This helps separate SQL instructions from user-supplied data and reduces the risk of SQL injection.

This is especially important in registration forms, login systems, online shopping applications, and other systems that accept data from users.

When Should INSERT INTO Be Used?

When a completely new record needs to be stored.
When application users submit new information.
When importing records into a table.
When transferring selected records between tables.
When initializing tables with required data.

If an existing record needs to be changed, use UPDATE rather than INSERT.

Advantages of INSERT INTO

Provides a simple way to add records.
Supports one or multiple rows.
Works with database constraints.
Can insert selected column values.
Can transfer records using INSERT INTO SELECT.
Works with application-driven database operations.

Limitations and Considerations

Invalid data can cause the operation to fail.
Constraint violations must be handled.
Very large insertion operations require performance planning.
Duplicate records can occur if database design does not prevent them.
Application code should use safe parameter handling.

Complete Practical Example

The following example demonstrates a basic workflow: creating a table, inserting multiple records, and checking the result.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    Course VARCHAR(50),
    Age INT
);

INSERT INTO Student
(StudentID, StudentName, Course, Age)
VALUES
(101, 'Rahul', 'BCA', 20),
(102, 'Priya', 'B.Tech', 21),
(103, 'Amit', 'MCA', 22);

SELECT * FROM Student;

The CREATE TABLE statement creates the structure, INSERT INTO adds the records, and SELECT verifies the stored data.

SQL INSERT INTO Interview Questions

1. What is INSERT INTO in SQL?

INSERT INTO is a SQL statement used to add new records to an existing table.

2. Is INSERT a DDL or DML command?

INSERT is generally classified as a Data Manipulation Language (DML) statement.

3. Can INSERT add multiple rows?

Yes. Many SQL systems support inserting multiple rows using one INSERT statement.

4. Is specifying column names mandatory?

Not always. However, explicitly specifying column names is recommended for clarity and maintainability.

5. Can INSERT add NULL values?

Yes, provided the target column permits NULL.

6. What happens when a primary key value is duplicated?

The database normally rejects the INSERT because a primary key must remain unique.

7. What is INSERT INTO SELECT?

It inserts the result of a SELECT query into another table.

8. Can INSERT use a WHERE clause?

The normal INSERT ... VALUES syntax does not use WHERE. A WHERE condition can be used with INSERT ... SELECT.

9. What is the difference between INSERT and UPDATE?

INSERT adds new rows, whereas UPDATE modifies values in existing rows.

10. What is the purpose of a DEFAULT value?

A DEFAULT provides a predefined value when a column is not supplied during insertion.

11. Can an AUTO_INCREMENT column be omitted from INSERT?

Yes, when the database is configured to generate its value automatically.

12. What can cause an INSERT statement to fail?

Common causes include invalid data types, duplicate keys, missing required values, foreign key violations, and other constraint violations.

13. How can inserted data be checked?

SELECT * FROM Student;

14. Why are parameterized queries recommended?

They help safely handle application input and reduce the risk of SQL injection.

15. When should INSERT be used instead of UPDATE?

INSERT should be used when a new record needs to be created. UPDATE should be used when an existing record needs to be modified.

Conclusion

The INSERT INTO statement is one of the fundamental SQL commands for storing new information in a database. It can be used to add a single row, insert multiple rows, populate selected columns, work with NULL and DEFAULT values, and transfer records between tables through INSERT INTO SELECT.

A good understanding of INSERT INTO also requires knowledge of primary keys, foreign keys, NOT NULL, UNIQUE, CHECK constraints, data types, and safe application practices. Once these concepts are clear, inserting reliable data into relational databases becomes much easier.

← Previous: TRUNCATE TABLE Next: SELECT Statement →