Table of contents

Introduction

By SQL INSERT INTO

2.1.

By using attribute/column names

2.2.

By not mentioning column name

Insert data through select statement

FAQs

Key Takeaways

Last Updated: Mar 27, 2024

INSERT Query

Q: (i) By using attribute/column names

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

Q: (ii) By not mentioning column name.

INSERT INTO table_nameVALUES (value1,value2,value3,...);

Author Purswani Dineshkumar

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

INSERT INTO is a command that is used to store data in tables. The INSERT statement adds a new entry to the table where data can be stored. In this blog, we will see various situations where INSERT INTO would be helpful and how to use this command.

Different ways of inserting data in a relational database

By SQL INSERT INTO

We can insert data or tuples in the table by using SQL INSERT INTO query command.

There are two different ways which are:

By using attribute/column names

Here we have to mention both the values we want to insert and the column name where we want to insert.

Syntax:

INSERT INTO table_name ( column1,column2,column3…)

VALUES(value1,value2,value3…);

Here

INSERT INTO `table_name` is the command that tells the MySQL server to add a new row into a table named `table_name.`(column_1,column_2,…) specifies the columns to be updated in the new MySQL row VALUES (value_1,value_2,…) specifies the values to be added into the new row

For Example,

Let’s say we have a table for students records as

Roll_No	Name	Address	Adhar_No
1	Amisha	Ahmedabad	10100101
2	Bhavisha	Pune	20200202
5	Akash	Ahmedabad	50500505

So, to insert a record, we will use the query as:

INSERT INTO STUDENTS(ROLL_NO, NAME, ADDRESS, ADHAR_NO)

VALUES(3,Anant,Udaipur,30300303);

INSERT INTO STUDENTS(ROLL_NO,NAME,ADDRESS,ADHAR_NO)

VALUES(6,Dun,Ahmedabad,60600606);

INSERT INTO STUDENTS(ROLL_NO,NAME,ADDRESS,ADHAR_NO)

VALUES(7,Shivani,Ahmedabad,70700707);

The final table after running the above queries will be:

Roll_No	Name	Address	Adhar_No
1	Amisha	Ahmedabad	10100101
2	Bhavisha	Pune	20200202
5	Akash	Ahmedabad	50500505
3	Anant	Udaipur	30300303
6	Dun	Ahmedabad	60600606
7	Shivani	Ahmedabad	70700707

By not mentioning column name

We don’t have to specify the column names where the data will be inserted in this method. We just mention their values.

Syntax:

INSERT INTO table_name

VALUES (value1,value2,value3,...);

HERE

INSERT INTO `table_name` is the command that tells the MySQL server to add a new row into a table named `table_name.`

VALUES (value_1,value_2,…) specifies the values to be added into the new row

For Example,

If we want to insert in the same student’s table, we will use the following query.

INSERT INTO STUDENTS

VALUES(4,Harsh,Gandhinagar,40400404);

The final table after both the queries will be:

Roll_No	Name	Address	Adhar_No
1	Amisha	Ahmedabad	10100101
2	Bhavisha	Pune	20200202
5	Akash	Ahmedabad	50500505
3	Anant	Udaipur	30300303
6	Dun	Ahmedabad	60600606
7	Shivani	Ahmedabad	70700707
4	Harsh	Gandhinagar	40400404

Also see, SQL EXCEPT

Insert data through select statement

This query is used when we first have to select records from a table using SELECT statement, and next, we insert into a table specified with INSERT INTO

Syntax

INSERT INTO table_name [(column1, column2, .... columnN)]

SELECT column1, column2, .... Column N

FROM table_name

[WHERE condition];

Example:

We had the student’s records in the existing STUDENTS table.

Let’s say if the question was that we had to create another table named Ahmedabad for students whose address is Ahmedabad. So the query, in this case, would be:

INSERT INTO AHMEDABAD

SELECT *

FROM STUDENTS

WHERE ADDRESS = ‘Ahmedabad’;

The result of the above query will be the following:

Roll No	Name	Address	Adhar No.
1	Amisha	Ahmedabad	10100101
5	Akash	Ahmedabad	50500505
6	Dun	Ahmedabad	60600606
7	Shivani	Ahmedabad	70700707

Also see, Tcl Commands in SQL

FAQs

1. What is the purpose of the INSERT statement?

Insert statement is to used to insert a single or multiple rows of data to a table in the database.

2. What is the syntax of the INSERT statement?

There are two different ways to use the INSERT statement, which are as follows:

(i) By using attribute/column names

INSERT INTO table_name ( column1,column2,column3…)

VALUES(value1,value2,value3…);

(ii) By not mentioning column name.

INSERT INTO table_name

VALUES (value1,value2,value3,...);

3. Is the INSERT statement a DDL statement or DML statement?

Insert statement is Data Manipulation Language (DML), which is used to manipulate data. A data manipulation language (DML) is a computer programming language that allows you to add (insert), delete (delete), and alter (update) data in a database. Structured Query Language (SQL), used to retrieve and manipulate data in a relational database, is a popular data manipulation language.

Key Takeaways

In this blog, we learned about Insert statements in SQL queries.

We begin with learning the syntax of these statements and how to use them in queries to insert rows of data into the table and solve various problems and examples of how the INSERT query will be useful in SQL.

Visit here to learn more about different topics related to database management systems.

Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.