Tutorial

How to INSERT Multiple Records in SQL

Published on August 3, 2022
author

Safa Mulani

How to INSERT Multiple Records in SQL

Hey, folks! In this article we will be focusing on ways to Insert Multiple rows in SQL.

Need of SQL Insert INTO Multiple rows query

SQL INSERT query inserts data into the columns of a particular table.

The normal SQL INSERT query inputs the data values in a single row. In case when we want to insert data in multiple rows at once, this query fails.

Thus, in order to save the execution time, we need to use the SQL INSERT query in such a manner that it injects data into multiple rows at once.

Having understood the need of SQL Insert query for multiple rows, let us get started with the implementation of the same.


Traditional SQL INSERT query to insert multiple records

Traditional SQL INSERT query injects input data into multiple rows. In this technique, we need to the insert query as many times we want to input data into the rows of the table.

The basic drawback of this query is the overhead of execution of every insert query for multiple rows injection.

Example:

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");
select * from Info;

Output:

1	100	Pune
2	50	Satara
3	65	Pune
4	97	Mumbai
5	12	USA

INSERT-SELECT-UNION query to insert multiple records

In the above section, we got to know that INSERT INTO query injects multiple records. But, if we observer the output of it, we get to know that the clause ‘INSERT INTO’ is repeated many times.

Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table.

The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.

create table Info(id integer, Cost integer);
INSERT INTO Info (id, Cost)  
SELECT 1, '123'  
UNION ALL   
SELECT 2, '234'  
UNION ALL  
SELECT 3, '456';  

select * from Info;

Output:

1	123
2	234
3	456

Row construction to insert multiple records

The SQL INSERT query is used in a manner wherein we make use of a single INSERT query to insert multiple records within a single point of execution.

Syntax:

INSERT INTO Table (columns)  
VALUES (val1, val2, valN); 

Example:

create table Info(id integer, Cost integer,city nvarchar(200));
INSERT INTO Info (id,Cost,city)  
VALUES (1,200, 'Pune'), (2, 150,'USA'), (3,345, 'France');  

select * from Info;

Output:

1	200	Pune
2	150	USA
3	345	France

Conclusion

By this we have come to the end of this topic. Herein we have covered three different techniques to INSERT data values across multiple records of a table.

Please feel free to comment in case you come across any doubt.

For more such posts related to SQL, please visit SQL JournalDev.


References

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors
Default avatar
Safa Mulani

author

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more