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;

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;

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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Introduction to Angular modules Part 3: NgModules vs JavaScript modules and Angular librariesAngularHow to create a Random Hex Color generator using JavaScript?JavaScriptHow to Use ROLLUP Operator in SQL and MySQL?SQL/MySQLINTERSECT and EXCEPT in PostgresPostgresWordPress: How to setup and get values from an ACF options page?WordPressHow to get the height and width of an element using JavaScript?JavaScript