SQL INSERT INTO

INSERT INTO adds new rows to a table.

INSERT INTO Syntax

The clearest way is to name the columns and then give a value for each one:

Example

sql

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

The values must be in the same order as the columns.

Insert One Row

A new student, Usman Ali from Hyderabad, joins the Python course:

Example

sql

INSERT INTO students (id, name, city, age, marks, course_id)
VALUES (7, 'Usman Ali', 'Hyderabad', 20, 81, 2);

Output

1 row inserted.

Now the table has a new row:

Example

sql

SELECT * FROM students WHERE id = 7;

id	name	city	age	marks	course_id
7	Usman Ali	Hyderabad	20	81	2

The exact message (such as Query OK, 1 row affected) depends on the database you use.

Insert Without Column Names

If you give a value for every column, in the table's own order, you may leave out the column list:

Example

sql

INSERT INTO courses
VALUES (5, 'Freelancing', 2);

This short form breaks easily. If someone adds or moves a column, the values land in the wrong places. Writing the column names is safer, and it is what most teams do.

Insert Data in Some Columns Only

You do not have to fill every column. Columns you leave out get their default value, or NULL if there is no default:

Example

sql

INSERT INTO students (id, name, city)
VALUES (8, 'Zainab Shah', 'Karachi');

id	name	city	age	marks	course_id
8	Zainab Shah	Karachi	NULL	NULL	NULL

A column marked NOT NULL without a default must always get a value, or the insert fails. You will learn about this in the constraints lesson.

Insert Several Rows at Once

Separate each group of values with a comma:

Example

sql

INSERT INTO courses (id, title, duration_months)
VALUES
  (6, 'AI Basics', 3),
  (7, 'UI/UX Design', 4),
  (8, 'WordPress', 2);

Output

3 rows inserted.

This is faster than three separate INSERT statements, because the database does the work in one go.

Automatic ID Numbers

In real tables you usually do not type the id yourself. The database creates the next number for you. Each database names this a little differently:

Database	How the id column is defined
MySQL	`id INT AUTO_INCREMENT PRIMARY KEY`
PostgreSQL	`id SERIAL PRIMARY KEY` or `GENERATED ALWAYS AS IDENTITY`
SQL Server	`id INT IDENTITY(1,1) PRIMARY KEY`
SQLite	`id INTEGER PRIMARY KEY`

Then you simply leave id out of the insert.

Note About the Sample Data

The rows added in this lesson are only examples. The following lessons use the original six students and four courses again, so their results stay easy to check.

Exercise

Which statement correctly adds a course called 'SEO' that lasts 2 months?

INSERT INTO courses (id, title, duration_months) VALUES (9, 'SEO', 2);INSERT courses SET (9, 'SEO', 2);ADD INTO courses VALUES (9, 'SEO', 2);INSERT INTO courses (id, title, duration_months) (9, SEO, 2);