How to insert into a table using loop based on another select statement

Question

I have a select query which returns some IDs.

SELECT GROUP_ID FROM GROUP_REQUEST_MAPPING WHERE REQUEST_TYPE_ID = 1

As a result I get this.

GROUP_ID
6
7
8
9
14

I have to loop through the IDs and then insert that many rows in another table.

INSERT INTO REQ_TASK VALUES(_,_,_,IDs)

How do I do that. I am new to sql. Thanks

You want to do this in SQL or from an application? How are you planning to get values for other columns of REQ_TASK table? Are they static for all the groupids? — Chetan, May 26 '17 at 05:16
@Chetan Ranpariya I am getting other column values based on the input parameters of the stored procedure. — Jyotirmaya Prusty, May 26 '17 at 05:19
Are the other values(_,_,) in REQ_TASK VALUES(_,_,_,IDs) some constants or picked up from different table — Kapil, May 26 '17 at 05:19
@Kapil Yes some values are constant and some are from the input parameters of the SP. — Jyotirmaya Prusty, May 26 '17 at 05:20

Kapil · Answer 1 · 2017-05-26T05:31:00.793

4

Directly use the constants or parameters with the select like below:

INSERT INTO REQ_TASK VALUES(_,_,_,IDs)
SELECT @param1,@param2,'xyz', GROUP_ID FROM GROUP_REQUEST_MAPPING WHERE REQUEST_TYPE_ID = 1

Here is a small example

 Create table #food
( item varchar(50))

insert into #food values 
('icecream'),
('sandwich'),
('Pasta'),
('FrenchFries'),
('Toast')

--Create another table #food_test

Create table #food_test
( item varchar(50),quantity int)

Insert into #food_test(item,quantity)
select item,10 from #food

Now check the value in #food_test

select * from #food_test

edited May 26 '17 at 05:31

answered May 26 '17 at 05:20

Kapil

987
5
11

Will this single statement of insert create 5 rows in the REQ_TASK table? – Jyotirmaya Prusty May 26 '17 at 05:21
yes, if the SELECT GROUP_ID FROM GROUP_REQUEST_MAPPING WHERE REQUEST_TYPE_ID = 1 returns 5 rows then – Kapil May 26 '17 at 05:22
You saved my day ! the answer really worked for me . just to be clear this statment actualy iterateed throgh the "list" the returns from the "select statment" ant isertets value after value from the first Tbl to the second Tbl. – haddassah S Nov 20 '22 at 15:20
Now I'm looking to make sure that if i already have such a "key-value" pair in the 2nd table it won't insert it again. – haddassah S Nov 20 '22 at 15:24

score 1 · Answer 2 · edited May 26 '17 at 06:41

1

You can use:

INSERT INTO REQ_TASK (col1, ...) 
SELECT GROUP_ID FROM GROUP_REQUEST_MAPPING 
WHERE REQUEST_TYPE_ID = 1;

edited May 26 '17 at 06:41

Arulkumar

12,966
14
47
68

answered May 26 '17 at 05:21

McMurphy

1,235
1
15
39

Ajay2707 · Answer 3 · 2017-05-26T07:13:47.150

You can directly use your query to insert in another table via insert into statement as

USE MASTER

 Create table GROUP_REQUEST_MAPPING
( GROUP_ID int, REQUEST_TYPE_ID int)

insert into GROUP_REQUEST_MAPPING values 
(6,1),
(7,1),
(8,1),
(9,1),
(10,1)
Create table REQ_TASK (AMOUNT int, IDs int)
SELECT * FROM GROUP_REQUEST_MAPPING
SELECT * FROM REQ_TASK

INSERT INTO REQ_TASK (AMOUNT,IDs)
SELECT 10, GROUP_ID FROM GROUP_REQUEST_MAPPING WHERE REQUEST_TYPE_ID = 1

SELECT * FROM REQ_TASK

DROP TABLE REQ_TASK
DROP TABLE GROUP_REQUEST_MAPPING

https://www.w3schools.com/sql/sql_insert_into_select.asp

How to insert into a table using loop based on another select statement

3 Answers3