How to loop statements in SQL Server

问题:

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)

1. The first statement will return multiple rows of ITEM_ID
2. By using the ITEM_ID I need to select the ITEM_PRICE from another table using the second statement
3. 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

回答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'               

回答2:

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'