Yogesh Chauhan's Blog

How to use a Subquery to Insert Multiple Rows in SQL Table?

in SQL/MySQL on January 10, 2020

There are many ways to insert data into a table. Some of them are easy ways, others are not. We use INSERT statement to insert data into a table and we use VALUES clause of INSERT statement to specify values for a single row. But we can use subquery to insert data as well, in fact multiple rows.

Let's take a look at the syntax of the INSERT statement which inserts rows from another table:


INSERT [INTO] table [or columns]
SELECT columns
FROM source_table
WHERE condition;

Let's take look at the following examples. Both of the examples will fetch rows from the invoices table and insert those rows into invoice_archives. Both invoices and invoice_archives have same columns but the payment_total and credit_total don't have default values in the table. So, we need to include values for those tables when we insert data into invoice_archives. 

This following query will insert paid invoices from the invoices table into invoices archives table.


INSERT INTO invoice_archives
SELECT * FROM invoices
WHERE invoice_total-payment_total-credit_total=0;

//output
5 rows inserted

The query above demonstrates how to use a subquery in an INSERT statement without writing column names. We have used *(asterisk) so all the columns will be fetched and then it will check for the condition in WHERE clause. All the rows will be added into invoice_archives which satisfy the condition.

This query is same as above but with column list:


INSERT INTO invoice_archives
(invoice_id, seller_id, invoice_number, invoice_total, credit_total, payment_total, conditions_id, invoice_date, invoice_due_date)
SELECT invoice_id, seller_id, invoice_number, invoice_total, credit_total, payment_total, conditions_id, invoice_date, invoice_due_date
FROM invoices
WHERE invoice_total-payment_total-credit_total=0;

//output
5 rows inserted

We have uses list of columns over here rather than *(asterisk). We can list the columns in any sequence as long as we are using the same sequence in the second list of columns. We can omit the columns with default values in this statement as well. 

Now you must be wondering where are the subqueries?

Well, those are just not coded in parentheses like a subquery in a SELECT statement. That's because they are not coded within a clause o the INSERT statement but they are coded in the place of VALUES if you notice. 

NOTE: If you want to make sure that you're inserting data into the right rows and columns then try to execute the SELECT statement by itself.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
The SQL UNION OperatorSQL/MySQLHow different is Handling Events in React vs HTML DOM?ReactHow to float an image around texts?CSSHow to add Lifecycle Methods to a Class in React?ReactHow to compile and watch Sass using Gulp in WordPress?SCSSHow to embed YouTube or other video links in WordPress?WordPress