SQL Insert Multiple Rows

How to Insert Multiple Rows in SQL?

We must first create a table to insert data in a row. The following commands will help us to create a table: 

create table CodingNinjas (
	ID int,
	Name varchar(255),
	age int,
	Address varchar(255),
	salary int
);

After creating the table, there are many ways to insert multiple rows in SQL. Let us look at some of the ways briefly.

Using Multiple INSERT Statements 

Using multiple INSERT statements allows you to insert multiple rows of data into a database table at once, reducing the need for repetitive code and improving efficiency.

Code:

insert into CodingNinjas values(10101,' Suresh',25,'Pune',25000);
insert into CodingNinjas values(10102,'Jaya',28,'Kanpur',30000);
insert into CodingNinjas values(10103,'Jatin',24,'Dhampur',40000);
insert into CodingNinjas values(10104,'Rohit',21,'Mumbai',45000);
insert into CodingNinjas values(10105,'Pratik',28,'Patna',50000);

Output:

Providing All Data in the VALUE Clause

By providing all the necessary data in the VALUE clause, you can add multiple rows to a table in a single INSERT statement, simplifying the code and improving efficiency.

Code:

INSERT INTO CodingNinjas (id, name, age, city, salary)
VALUES 
	(10101, 'Suresh', 25, 'Pune', 25000),
	(10102, 'Jaya', 28, 'Kanpur', 30000),
	(10103, 'Jatin', 24, 'Dhampur', 40000),
	(10104, 'Rohit', 21, 'Mumbai', 45000),
	(10105, 'Pratik', 28, 'Patna', 50000);

Output:

Also see, SQL EXCEPT

Using Select and Union to Add Rows in a Table

You can efficiently add multiple rows by combining data from other tables or queries using the SELECT and UNION statements, then inserting the resulting rows into a table.

Code:

INSERT INTO CodingNinjas ( ID, Name, age, Address, salary )
SELECT  10101,'Suresh',25,'Pune',25000
UNION ALL
SELECT 10102,'Jaya',28,'Kanpur',30000
UNION ALL
SELECT 10103,'Jatin',24,'Dhampur',40000
UNION ALL
SELECT 10104,'Rohit',21,'Mumbai',45000
UNION ALL
SELECT 10105,'Pratik',28,'Patna',50000;

Output:

Shorter Way to Insert Multiple Rows in SQL

You can insert several rows into a table efficiently by using the VALUES clause within a single INSERT INTO statement to specify values for each row.

create table Info(id integer, Cost integer);
INSERT INTO Info (id, Cost)  
VALUES ( 1, '123'), (2, '234'), (3, '456') ; 
select * from Info;

Output:

Click here to know about, pwd command in linux

Benefits of Inserting Multiple Rows

Let us look at the benefit of SQL insert multiple rows with the help of an example.

Handling large amounts of data: It's as if you were trying to fill a jar with marbles. You could put them in one by one, but it would take a long time, and you might drop some or put them in the wrong spot. Putting them in a handful at a time is faster, and you're less prone to make mistakes.

In the same manner, inserting multiple rows at once is simpler and easier to understand than inserting them one by one. It also saves memory and is a better approach when working with large amounts of data.

So entering multiple rows at once is a great option when dealing with large databases, making it easier, faster, and less complex code and memory usage.

Frequently Asked Questions

Q. What is the insert command used for?

The INSERT command is a widely used SQL statement in databases that enables the addition of new data to a table. It allows the user to insert a new row or multiple rows of data into an existing table.

Q. Can you insert multiple rows at once in SQL?

To put data into several rows of the table, you can use the put-SELECT-UNION query. The SQL UNION query assists in selecting all of the data enclosed by the SELECT query via the INSERT statement.

Q. How to insert 3 rows in SQL?

To insert three rows in SQL, use the 'INSERT INTO' statement followed by the table name and values for each row between parenthesis, separated by commas. For example:
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), (value5, value6);

Q. Is it possible to INSERT multiple rows using Union in SQL?

We can use the INSERT-SELECT-UNION query to insert data into many rows of the table. The SQL UNION query helps select all the data enclosed by the SELECT query using the INSERT statement.

Conclusion

In this article, you have learned about the insert command in SQL, different ways to insert multiple rows in SQL, and the benefits of inserting multiple rows simultaneously. We hope this article helped you more about how to insert multiple rows in  SQL.

If you want to learn more, refer to these articles:

• INSERT Query
   
• Basic SQL Commands and Queries
   
• TCL Commands in SQL
   
• SQL Syntax
   
• Sql functions

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninja!