Summary -

In this topic, we described about the INSERT Statement with detailed example.

INSERT statement is used to insert one or more rows into table. To insert the data into th table, we should have insert privilege on the table.

Syntax -

INSERT INTO table_name (column1, column2, ..., columnN)
 VALUES (value1, value2, ..., valueN); 
  • column1, column2, …, columnN – Specifies the column names from table.
  • table_name – Specifies the name of the table.
  • value1, value2, …, valueN - Specifies the values used to insert into the table.

Insert all values to all existing columns in table, doesn't require to specify the column names in the INSERT query. The syntax was as shown in below –

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

Example –

Let us consider an employee_details table created with emp_id, emp_name, designation, manager_id, date_of_hire, salary, dept_id columns.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Scenario – Insert a row with specified column values.

Requirement – Insert a row into employee_details table with column names in INSERT Statement. The query was as follows –

INSERT INTO employee_details(emp_id,emp_name,designation,date_of_hire,
salary,dept_id)VALUES(001,"Employee1","Director","2019-11-07",45000,
1000); 

By executing above query, we can insert employee details with specified column values by skipping manager id. The table after insert was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000

Scenario – Insert a row with all column values using INSERT statement.

Requirement – Insert multiple rows into employee_details table with all column values. The query was as follows –

INSERT INTO employee_details(emp_id,emp_name,designation,manager_id,
date_of_hire,salary,dept_id)VALUES(003,"Employee3","Manager","Employee1",
"2019-11-07",25000,1000 );  

The query also written as –

INSERT INTO employee_details VALUES(003,"Employee3","Manager",
"Employee1","2019-11-07", 25000, 1000);  

By executing above query, we can insert two rows into the employee_details table. The table after insert was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
003 Employee3 Manager Employee1 2019-11-07 25000.00 1000

Scenario – Insert multiple rows with all column values using INSERT statement.

Requirement – Insert multiple rows into employee_details table with all column values. The query was as follows –

INSERT INTO employee_details VALUES(005,"Employee5","Analyst",
"Employee3","2019-07-11", 20000, 1000), (006, "Employee6", "Analyst",
 "Employee3","2019-10-08", 20000, 1000);      

By executing above query, we can insert two employee details with all column values. The table after insert was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
003 Employee3 Manager Employee1 2019-11-07 25000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 20000.00 1000