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

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.

Share this post