SQL INSERT Query

What is an “INSERT” query?

Every table in database need this query as first and formal requirements. INSERT query is sql statement which execute within database to pass data to sql engine via syntax and sql engine store that data into table.

In basic terms, we can say INSERT query is used to create or store rows into table in database.

Why we need “INSERT” query?

Once we create table into database, it is necessary to add records into database tables. So here, main use of INSERT query is to store data into tables.

There are two ways to store data into table.

  • Write INSERT query in SQL Editor and execute that
  • Use user interface to add records directly using columns structure.

In this chapter, we will learn how we can use INSERT query to add records into database tables.

When you use INSERT query to add records into table, you may either add records for all columns or you may also add records for specified columns of table. It completely depends on what is your requirement to add records into database tables. We will learn both options to add records into database tales.

Syntax for INSERT query

  • INSERT with specified columns
    INSERT INTO TABLENAME (Column1, Column2, Column3, Column4, ...)
    VALUES (ColumnValue1, ColumnValue2, ColumnValue3, ColumnValue4, ...);
  • INSERT with all columns
    INSERT INTO TABLENAME
    VALUES (ColumnValue1, ColumnValue2, ColumnValue3, ColumnValue4, ColumnValue5, ...);

In above syntax, we can see there are two type of INSERT query which we can use to store data into table. First INSERT query explain n such a way where we can store data into specified columns only. Sometime it happen, that all columns doesn’t required data in table so in that case, we can use first option with above syntax to store data in table.

Another option is to store data into all columns in table. That means, in another option of above syntax, we can store data into all columns into table and in that case we don’t need to define columns name in INSERT query.

Let’s understand more in following example.

Data with Specified Columns in table (INSERT with specified columns)

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter
2 EMP_1002 Josh Wills
3 EMP_1003 Jeremy Parkar

Data in all columns (INSERT with all columns)

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990

Let’s understand more in following example.

Note: In following example, Id field is primary key column and auto number so it will be added automatically with each row. We don’t have to define any value for that in INSERT query.

Example 1 (Insert data into specified columns)

INSERT INTO Employee (EmployeeCode, FirstName, LastName) VALUES ('EMP_1001', 'John', 'Peter');
INSERT INTO Employee (EmployeeCode, FirstName, LastName) VALUES ('EMP_1002', 'Josh', 'Wills');
INSERT INTO Employee (EmployeeCode, FirstName, LastName) VALUES ('EMP_1003', 'Jeremy', 'Parkar');

Output Results:

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter
2 EMP_1002 Josh Wills
3 EMP_1003 Jeremy Parkar

Example 2 (Insert data into all columns)

INSERT INTO Employee (EmployeeCode, FirstName, LastName, Gender, BirthDate) VALUES ('EMP_1001', 'John', 'Peter', 'Male', '10-11-1982');
INSERT INTO Employee (EmployeeCode, FirstName, LastName, Gender, BirthDate) VALUES ('EMP_1002', 'Josh', 'Wills', 'Male', '09-07-1980');
INSERT INTO Employee (EmployeeCode, FirstName, LastName, Gender, BirthDate) VALUES ('EMP_1003', 'Jeremy', 'Parkar', 'Female', '08-09-1990');

Output Results:

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990