Inserting records using subqueries

In this page, we are discussing the inserting rows of another table using subquery.

Example:

Sample table: agent1

Sample table: agents

To insert all records into 'agent1' table from 'agents' table, the following SQL statement can be used:

SQL Code:

-- This SQL code attempts to insert all rows from the 'agents' table into the 'agent1' table.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM  agents;
-- Selects all columns and rows from the 'agents' table to be inserted into the 'agent1' table

Explanation:

• This SQL code aims to copy all rows from the 'agents' table to the 'agent1' table.


• The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.


• The SELECT statement retrieves all columns and rows from the 'agents' table.


• The '*' wildcard is used to select all columns from the 'agents' table.


• As a result, all rows from the 'agents' table will be inserted into the 'agent1' table, effectively copying the data from one table to another.

Inserting records using subqueries with where clause

In this page we are discussing, how to insert rows using INSERT INTO statement, where rows are results of a subquery, made up of SQL SELECT statement with WHERE clause.

Example:

Sample table: agent1

Sample table: agents

To insert records into 'agent1' table from 'agents' table with the following condition -

1. 'working_area' of 'agents' table must be 'London',

the following SQL statement can be used:

SQL Code:

-- This SQL code attempts to insert selected rows from the 'agents' table into the 'agent1' table, filtered by a condition.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM  agents
-- Selects all columns and rows from the 'agents' table
WHERE working_area="London"
-- Filters the rows selected from the 'agents' table based on the condition that the 'working_area' column equals "London"

Explanation:

• This SQL code aims to copy selected rows from the 'agents' table to the 'agent1' table, specifically those with "London" as the working area.


• The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.


• The SELECT statement retrieves all columns and rows from the 'agents' table.


• The WHERE clause filters the rows selected from the 'agents' table based on the condition that the 'working_area' column equals "London".


• As a result, only the rows with "London" as the working area from the 'agents' table will be inserted into the 'agent1' table.

SQL inserting records using subqueries with any operator

In the following we are going to discuss, how an ANY operator can participate in an INSERT INTO statement.

Example:

Sample table: agent1

Sample table: agents

Sample table: customer

To insert records into 'agent1' table from 'agents' table with the following conditions -

1. 'agent_code' of 'agents' table must be any 'agent_code' from 'customer' table which satisfies the condition bellow :

2. 'cust_country' of customer table must be 'UK',

the following SQL statement can be used:

SQL Code:

-- This SQL code attempts to insert selected rows from the 'agents' table into the 'agent1' table, filtered by a condition involving a subquery.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM  agents
-- Selects all columns and rows from the 'agents' table
WHERE agent_code=ANY(
-- Specifies a condition involving a subquery to filter the rows from the 'agents' table
SELECT agent_code FROM customer
-- Selects the 'agent_code' column from the 'customer' table in the subquery
WHERE cust_country="UK");
-- Filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from customers located in the UK

Explanation:

• This SQL code aims to copy selected rows from the 'agents' table to the 'agent1' table, based on a condition involving a subquery.


• The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.


• The SELECT statement retrieves all columns and rows from the 'agents' table.


• The WHERE clause includes a condition involving a subquery. The subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'cust_country' must be "UK".


• The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from customers located in the UK.


• As a result, only the rows from the 'agents' table corresponding to agents associated with customers in the UK will be inserted into the 'agent1' table.

SQL insert using subqueries with any operator and group by

In the following we are going to discuss, how an ANY operator with GROUP BY clause can participate in an INSERT INTO statement.

Example:

Sample table: agent1

Sample table: agents

Sample table: customer

Sample table: orders

To insert records into 'agent1' table from 'agents' table with the following conditions -

'agent_code' of agents table must be any 'agent_code' from 'customer' table which satisfies the condition bellow :

'agent_code' of customer table must be any 'agent_code' from 'orders'

table which satisfies the condition bellow :

same 'agent_code' of 'customer' table should come in a group,

'advance_amount' of 'orders' table must be more than 600,

the following SQL statement can be used:

SQL Code:

-- This SQL code attempts to insert selected rows from the 'agents' table into the 'agent1' table, filtered by nested subqueries.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM  agents
-- Selects all columns and rows from the 'agents' table
WHERE agent_code=ANY(
-- Specifies a condition involving a subquery to filter the rows from the 'agents' table
SELECT agent_code FROM customer
-- Selects the 'agent_code' column from the 'customer' table in the subquery
WHERE agent_code =ANY(
-- Specifies another condition involving a nested subquery to further filter the rows
SELECT agent_code FROM orders
-- Selects the 'agent_code' column from the 'orders' table in the nested subquery
WHERE  advance_amount>600)
-- Filters the rows selected from the 'orders' table based on the condition that the 'advance_amount' is greater than 600
GROUP BY agent_code);
-- Groups the rows selected from the 'customer' table by 'agent_code' and retrieves the unique agent codes
-- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the nested subquery result

Explanation:

• This SQL code aims to copy selected rows from the 'agents' table to the 'agent1' table, based on nested subqueries involving conditions from the 'customer' and 'orders' tables.


• The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.


• The SELECT statement retrieves all columns and rows from the 'agents' table.


• The WHERE clause includes a condition involving a nested subquery. The innermost subquery selects the 'agent_code' column from the 'orders' table, filtering the rows based on the condition that the 'advance_amount' must be greater than 600.


• The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result.


• The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result.


• As a result, only the rows from the 'agents' table corresponding to agents associated with customers having orders with an advance amount greater than 600 will be inserted into the 'agent1' table.

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Inserting the result of a query in another table
Next: Insert using nested subqueries with any operator