YogeshChauhan . com

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 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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Tackle Accessibility in React with JSXReactWhat is Object Mutability in JavaScript?JavaScriptHow to create a Star Ratings using CSS?CSSWhat are Class Constants in PHP?PHPWhat is a “promise” in JavaScript?JavaScriptThe 8 Golden Rules of Programming I Have Learned in My CareerMisc