How to loop statements in SQL Server

Question

I am new to SQL Server, I am trying to do something as follows.

Sample code :

SELECT ITEM_ID
FROM 'TABLE_NAME_1'
WHERE ITEM_STATUS = 'ACTIVE'

SET @ITEM_PRICE = (SELECT PRICE
                   FROM 'TABLE_NAME_2'
                   WHERE 'PRODUCT_ID' = 'ITEM_ID')

INSERT INTO 'TABLE_NAME_3' (ITEM_ID, PRICE)
VALUES (@ITEM_ID, @ITEM_PRICE)

The first statement will return multiple rows of ITEM_ID
By using the ITEM_ID I need to select the ITEM_PRICE from another table using the second statement
By using the third statement, I need to insert the data into the third table

Here the first statement returns only one ITEM_ID, then everything is easy to do. I f it returns multiple rows how can I do all these processes for all the ITEM_ID which has returned by the first statement?

Actually, If the first statement returns 5 rows I need to loop 5 times.

Is it possible in SQL Server, if yes, please help me to do this

George Joseph · Accepted Answer · 2018-12-14 05:07:57Z

1

Question would be why not use a straight SQL

INSERT 
  INTO 'TABLE_NAME_3' 
       (ITEM_ID
        ,PRICE
       )
 SELECT ITEM_ID,ITEM_PRICE
   FROM 'TABLE_NAME_1' A
   JOIN 'TABLE_NAME_2' B
     ON A.ITEM_ID=B.PRODUCT_ID
  WHERE A.ITEM_STATUS = 'ACTIVE'

answered Dec 14, 2018 at 5:07

George Joseph

5,93213 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Joseph Over a year ago

As the first option i would consider straight sql instead of looping logic. Straight SQL makes the query more efficient

Zhu Over a year ago

If multiple Items has the Status as 'Active',will it insert the data for all those items .?

Mustufa · Accepted Answer · 2018-12-14 05:14:47Z

0

based on your question i have created sample code you can use only one query to insert multiple data if you want to insert common data between table 1 and table 2 then use inner join or left join will be fine.

Code

INSERT INTO 'TABLE_NAME_3' (ITEM_ID,PRICE)
SELECT T1.ITEM_ID , T2.PRICE
FROM 'TABLE_NAME_1' AS T1
INNER JOIN 'TABLE_NAME_2' AS T2 ON T2.PRODUCT_ID = T1.ITEM_ID
WHERE T1.ITEM_STATUS = 'ACTIVE'

answered Dec 14, 2018 at 5:14

Mustufa

1314 bronze badges

Collectives™ on Stack Overflow

How to loop statements in SQL Server

2 Answers 2

2 Comments

Code

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Code

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related