Summary -

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

INSERT INTO VALUES statement is used to insert either all column values or specified column values in the table.

Inserting only specified columns data in the row requires the column names should be specified in the INSERT statement.

If we have all columns data, then there is no need to specify column names in the INSERT query.

Syntax -

INSERT INTO table_name(column1,column2,column3, ...columnp)
VALUES (value1, value2, value3, ...valuep); 
  • column1, column2, column3,..columnp- Represents the column names which are used to fetch from table.
  • table_name – Represents the name of the table.
  • value1, value2, value3,..valuep - Represents the values which are used to insert into the table.

Examples -

Scenario – Inserting specified columns data in table.

Requirement – Insert data for the columns emp_id, emp_name, designation, date_of_hire, salary, dept_id into employee_details. 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.00,1000);        

By executing above query, we can insert specified column values in employee table by skipping manager_id column and non-specified column updates with NULL value. The output was as follows –

employee_details -

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

Scenario – Inserting all column values.

Requirement – Inserting multiple rows data into employee_details table. The query was as follows –

INSERT INTO employee_details VALUES
(003,'Employee3','Manager','Employee1','2019-11-07',25000.00,1000),
(004,'Employee4','Manager','Employee2','2019-10-08',25000.00,2000);  

By executing above query, we can insert two rows into the employee_details table. The output 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
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000

Scenario – Inserting NULL values in table.

Requirement – Insert NULL values intentionally into employee_details table. The query was as follows –

INSERT INTO employee_details VALUES
(002,'Employee2','Director',NULL,'2019-11-07',40000.00, 2000); 

By executing above query,we can insert a row with manager_id as NULL into the employee_details table.The output 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
002 Employee2 Director 2019-11-07 40000.00 2000
003 Employee3 Manager Employee1 2019-11-07 25000.00 1000
004 Employee4 Manager Employee2 2019-11-07 25000.00 2000

Scenario – Inserting rows in views.

Requirement – Insert rows in view employee_v.

Let us assume a view with employee_v has the columns emp_id, emp_name, designation from employee_details table. The query was as follows –

INSERT INTO employee_v VALUES(005,'Employee5','Analyst'); 

By executing above query, we can insert values in the employee_v view. The table columns defined in the view inserted with the values specified in the INSERT statement. The remaining table columns not in the view are inserted by a default value or NULL. The output was as follows –

employee_v -

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
002 Employee2 Director 2019-11-07 40000.00 2000
003 Employee3 Manager Employee1 2019-11-07 25000.00 1000
004 Employee4 Manager Employee2 2019-11-07 25000.00 2000
005 Employee5 Analyst