INSERT Statement in SQL

The INSERT statement in SQL is used to add new rows of data into a table. You can insert a single row, multiple rows, or use the result of a query to populate data into a table.

Syntax for INSERT

1. Insert Single Row

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

table_name: The name of the table where data will be inserted.
column1, column2, ...: The columns you want to populate.
value1, value2, ...: The values to insert into the specified columns.

2. Insert All Columns

If you want to insert values into all columns, you can skip the column names:

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

Note: The values must match the column order and types in the table.

3. Insert Multiple Rows

INSERT INTO table_name (column1, column2, ...) 
VALUES 
(value1a, value2a, ...),
(value1b, value2b, ...);

4. Insert Using a Query

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2
FROM another_table
WHERE condition;

Basic Example

Insert a Single Row

INSERT INTO Students (StudentID, Name, Age, Class) 
VALUES (101, 'Alice', 14, '8th');

Insert All Columns

INSERT INTO Students 
VALUES (102, 'Bob', 13, '8th');

Insert Multiple Rows

INSERT INTO Students (StudentID, Name, Age, Class) 
VALUES 
(103, 'Charlie', 15, '8th'),
(104, 'Diana', 14, '8th');

Insert Using a Query

INSERT INTO Graduates (GraduateID, Name)
SELECT StudentID, Name
FROM Students
WHERE Age > 13;

Variants of INSERT

Using DEFAULT Values
- You can leave out columns with DEFAULT values, and the database will fill them in.

    INSERT INTO Employees (EmployeeID, Name)
    VALUES (1, 'John Doe');

ON DUPLICATE KEY UPDATE (MySQL)
- Handle conflicts by updating the row if the primary key already exists.

    INSERT INTO Products (ProductID, Name, Price)
    VALUES (1, 'Laptop', 1000)
    ON DUPLICATE KEY UPDATE 
    Price = 1000;

RETURNING Clause (PostgreSQL)
- Return the inserted values or generated keys.

    INSERT INTO Orders (CustomerID, OrderDate)
    VALUES (101, CURRENT_DATE)
    RETURNING OrderID;

Explaining the Statement

How INSERT Works

Adds data to the specified table.
Validates that the values match the data types and constraints of the table.
Fails if any constraints (e.g., NOT NULL, UNIQUE) are violated.

What Happens Behind the Scenes?

The database engine locks the table (or rows) momentarily during the insert operation to ensure data consistency.
If constraints like FOREIGN KEY are used, the database checks related tables to validate the data.

Common Errors and Solutions

Error	Cause	Solution
Column count doesn't match value count	The number of columns specified doesn't match the number of values.	Ensure the columns and values match.
Constraint violation	Attempting to insert data that violates constraints like `NOT NULL`.	Provide valid data that satisfies all constraints.
Data type mismatch	The value inserted doesn't match the column's data type.	Ensure the values have the correct data type (e.g., text, int).

SQL Representation

Create Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Class VARCHAR(10)
);

Insert Data

NSERT INTO Students (StudentID, Name, Age, Class) 
VALUES 
(101, 'Alice', 14, '8th'),
(102, 'Bob', 13, '8th'),
(103, 'Charlie', 15, '8th');

The INSERT statement populates the table with rows of student data, ensuring clean and structured entries.