YogeshChauhan.com
How to use a Subquery to Insert Multiple Rows in SQL Table?
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.

dreamhost

Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?



Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?



You might also like these

Safe Practice to add Links to cross-origin destinationUI/UX3 Ways we can create URLSearchParams Objects in JavaScriptJavaScriptHow to create a Star Ratings using CSS?CSSAccessing and Setting features of JavaScript ObjectsJavaScriptHow to Clone Objects in PHP?MiscFULL OUTER JOIN in PostgresPostgres