How to Insert Multiple Rows in SQL?

Structured Query Language(SQL) is a conventional language mainly used to interact with relational databases such as SQL Server, MySQL, Oracle, etc. Such interactions include storing, manipulating, and retrieval of data.

SQL comprises multiple commands that serve different purposes. SQL commands can be divided into four categories:

DML – Data Manipulation Language DDL – Data Definition Language DCL – Data Control Language DQL – Data Query Language

Data manipulation operations of SQL can be used to store data and insert multiple rows in sql.

Create a Database:

Query:

Ex:

Using the Database:

Query:

Ex:

Create a Table:

Query:

Ex:

How to INSERT Multiple Rows in SQL

1. Inserting individual rows into the table

To insert multiple rows in sql this method is useful. Multiple INSERT statements are used to put data into tables.

Query:

Ex:

Some limitations of this method:

1. The smaller number of rows is preferable, as this is a slow technique.
2. Repetition of code.
3. Any error between the lines would stop the program. But the lines before the error would get added.

2. Using INSERT INTO and SELECT Statement

This method effectively copies data from one table (source table) to another table (target table). Only the data types in both tables should match.

The query used to copy the whole table:

Query:

And, to take some of the columns:

Query:

Ex:

3. The Union Operator

A UNION operator is the third way to insert multiple rows in SQL. It is used to merge the results of multiple SELECT statements. It involves INSERT INTO...SELECT FROM statement to insert a different set of values rather than copying from some source table.

Query:

Ex:

4. Is There a Shorter Way to Insert Multiple Rows in SQL?

Adding multiple rows in tables can be done in different ways. However, a shorter method exists that is efficient and solves the problem of repetitive code.

Instead of writing a different INSERT statement, We add multiple rows into one INSERT statement.

Query:

Ex:

Similarly, many rows can be added at the same time directly.

Read more about union and union all from this article.