|
Getting your Trinity Audio player ready...
|
You can create the most perfect database in the world…
But without data, it is useless 😄
In this article, you will learn:
- What is INSERT in SQL
- How to insert single row
- How to insert multiple rows
- How to insert data into related tables
- Real FMCG company examples
Our primary focus is PostgreSQL, but we will show MySQL syntax side-by-side wherever needed.
What is INSERT in SQL?
INSERT is used to add new records into a table.
Basic syntax:
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);Very simple:
- Table name
- Column names
- Values in same order
Also Read: Create Database & Tables in SQL
Insert Data into Departments Table
Let’s start with our FMCG company.
PostgreSQL
INSERT INTO departments (department_name, location)
VALUES ('Sales', 'Raipur');Since id is auto-generated (SERIAL), we don’t need to insert it manually.
MySQL
INSERT INTO departments (department_name, location)
VALUES ('Sales', 'Raipur');Same syntax here.
Insert Multiple Rows at Once
Instead of inserting one by one:
INSERT INTO departments (department_name, location)
VALUES
('Finance', 'Bilaspur'),
('HR', 'Raipur'),
('Warehouse', 'Durg');This works in both PostgreSQL and MySQL.
Much faster and professional way 👍
Insert Data into Employees Table
Remember:
Employees table has a Foreign Key → department_id
So department must exist first.
PostgreSQL
INSERT INTO employees
(employee_code, first_name, last_name, salary, joining_date, department_id)
VALUES
('EMP001', 'Amit', 'Sharma', 45000.00, '2023-01-15', 1),
('EMP002', 'Priya', 'Verma', 38000.00, '2022-11-10', 2);MySQL
Same syntax:
INSERT INTO employees
(employee_code, first_name, last_name, salary, joining_date, department_id)
VALUES
('EMP001', 'Amit', 'Sharma', 45000.00, '2023-01-15', 1),
('EMP002', 'Priya', 'Verma', 38000.00, '2022-11-10', 2);Insert Data into Customers Table
INSERT INTO customers
(customer_name, phone, email, city, state)
VALUES
('Ravi Traders', '9876543210', 'ravi@email.com', 'Raipur', 'Chhattisgarh'),
('Sharma Stores', '9123456780', 'sharma@email.com', 'Bilaspur', 'Chhattisgarh');Works in both databases.
Insert Data into Products Table
PostgreSQL:
INSERT INTO products
(product_name, category, price, cost_price, launch_date)
VALUES
('Classic Cola', 'Beverages', 25.00, 18.00, '2023-05-01'),
('Potato Chips', 'Snacks', 20.00, 12.00, '2023-06-10');MySQL:
Same syntax (only difference internally is DECIMAL vs NUMERIC, but insert is same).
Insert Data into Sales Table (Most Important)
Now comes the powerful part.
Sales table connects:
- customer_id
- product_id
- employee_id
PostgreSQL
INSERT INTO sales
(invoice_no, customer_id, product_id, employee_id, quantity, total_amount, sale_date)
VALUES
('INV001', 1, 1, 1, 10, 250.00, '2024-01-15'),
('INV002', 2, 2, 2, 5, 100.00, '2024-01-16');MySQL
Same syntax:
INSERT INTO sales
(invoice_no, customer_id, product_id, employee_id, quantity, total_amount, sale_date)
VALUES
('INV001', 1, 1, 1, 10, 250.00, '2024-01-15'),
('INV002', 2, 2, 2, 5, 100.00, '2024-01-16');Important Rules While Inserting Data
1. Foreign Key must exist
If customer_id = 5 does not exist in customers table:
Insert will fail ❌
This protects data integrity.
2. Data Type Must Match
If salary column is NUMERIC:
You cannot insert:
'forty thousand'It will throw error.
3. NOT NULL Columns Must Be Provided
If column is NOT NULL:
You must provide value.
Insert Without Mentioning Column Names (Not Recommended)
You can do:
INSERT INTO departments
VALUES (DEFAULT, 'IT', 'Raipur', CURRENT_TIMESTAMP);But this is risky.
If column order changes, query will break.
Professional rule:
Always mention column names.
Insert Data and Return Inserted Row (PostgreSQL Special Feature)
PostgreSQL allows:
INSERT INTO departments (department_name, location)
VALUES ('Marketing', 'Raipur')
RETURNING *;It immediately shows inserted row.
MySQL does not support RETURNING in older versions.
Insert Data from Another Table
Advanced usage:
INSERT INTO inventory (product_id, stock_in, current_stock)
SELECT id, 100, 100
FROM products;This copies product IDs into inventory table.
Works in both PostgreSQL and MySQL.
Real-World Data Analyst Scenario
In real jobs (MIS / ERP / Government systems):
You will:
- Insert cleaned data into reporting tables
- Insert data into staging tables
- Insert audit logs
- Insert aggregated data
Understanding INSERT is not optional.
It is foundational for CRUD operations.
PostgreSQL vs MySQL Differences (INSERT Perspective)
| Feature | PostgreSQL | MySQL |
|---|---|---|
| Auto ID | SERIAL | AUTO_INCREMENT |
| RETURNING | Yes | Limited |
| Multiple rows insert | Yes | Yes |
| Insert from select | Yes | Yes |
Main syntax is almost identical.
Quick Summary
INSERT is used to add data into tables.
You can:
- Insert single row
- Insert multiple rows
- Insert using SELECT
- Insert into related tables
Rules to remember:
- Foreign key must exist
- Data type must match
- NOT NULL must be filled
Without INSERT, database remains empty.
What’s Next?
Next powerful articles: