SQL INSERT INTO Statement - Adding Data to SQL Tables

INSERT INTO Statement

Tables are like empty registers — they need entries to become useful. That’s where the INSERT INTO statement steps in. It lets you add rows of meaningful data into your SQL tables — student records, teacher assignments, exam scores — all start here.

Syntax of INSERT INTO

Standard Syntax

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

Short Syntax (Only when inserting all columns in order)

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

1. Example - INSERT Single Record

Assume we already created a students table:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

Now let’s insert data for a student named Arjun:

INSERT INTO students (roll_no, name, class, age, city)
VALUES (1, 'Arjun Deshmukh', '10A', 15, 'Pune');

Table Contents after INSERT

SELECT * FROM students;

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune

2. Inserting Multiple Records

Rather than inserting one record at a time, you can insert many in a single statement:

INSERT INTO students (roll_no, name, class, age, city)
VALUES
(2, 'Priya Reddy', '9B', 14, 'Hyderabad'),
(3, 'Karan Mehta', '10A', 15, 'Ahmedabad'),
(4, 'Neha Iyer', '8C', 13, 'Chennai');

Table Contents after INSERT

SELECT * FROM students;

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Ahmedabad
4	Neha Iyer	8C	13	Chennai

3. Inserting Only Specific Columns

If you don't have data for all columns, you can skip them — but you must exclude them in the column list as well:

INSERT INTO students (roll_no, name, class)
VALUES (5, 'Devanshi Shah', '9A');

Table Contents after INSERT

SELECT * FROM students;

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Ahmedabad
4	Neha Iyer	8C	13	Chennai
5	Devanshi Shah	9A	null	null

4. What If You Miss Required Columns?

Let’s try inserting without specifying roll_no (which is PRIMARY KEY):

INSERT INTO students (name, class, age, city)
VALUES ('Ishaan Pandey', '10B', 15, 'Lucknow');

ERROR: Column 'roll_no' cannot be null

This teaches an important lesson — columns marked NOT NULL or PRIMARY KEY must always be provided.

5. INSERT with DEFAULT Values

Suppose we use the students table, where the city column has a default value of 'Delhi':

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10),
  age INT,
  city VARCHAR(30) DEFAULT 'Delhi'
);

Now insert a student record without specifying the city column:

INSERT INTO students (roll_no, name, class, age)
VALUES (1, 'Mehul Arora', '10A', 15);

Table Contents after INSERT

SELECT * FROM students;

roll_no	name	class	age	city
1	Mehul Arora	10A	15	Delhi

Since no city was provided, SQL automatically filled in the default value 'Delhi'.

Best Practices for INSERT INTO

• Always specify column names for clarity and future safety.
• Validate data types — inserting text into a number column will cause errors.
• Insert in batches if you have many records.