Inserting records using subqueries
In this page, we are discussing the inserting rows of another table using subquery.
Example:
Sample table: agent1
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ........ | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table : agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ............ | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | .......... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table : agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ......... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | .......... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table : agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ........... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table : customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | .......... | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ......... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table : agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | .......... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table : customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | .......... | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
..........
200102 2000 300 25-MAY-08 C00012 A012
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.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
PREV : Inserting the result of a query in another table
NEXT : Insert using nested subqueries with any operator