Loop Statements without cursor and while in SQL

Uday Suragani

6y2.8k

Is there a way to loop through a table variable in TSQL without using a cursor and while?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Joginder Banger6y ago
Accepted answer
I got your problem.
Bulk insert:- suppose i have two table one is Instable and another is seltable. I want insert multiple records without looping.
INSERT INTO Instable
SELECT * FROM seltable
WHERE condition;
Bulk updated : if you want multiple records using join. before join the make a temp table and set the data as per records. after that use temp table in join.
UPDATE im
SET mf_item_number = gm.SKU --etc
FROM item_master im
JOIN group_master gm
    ON im.sku = gm.sku
JOIN Manufacturer_Master mm
    ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND
      gm.manufacturerID = 34
above code is just for reference, don't be confused.

thanks
JSB
0
Joginder Banger6y ago
I don't why are you need the looping on inserted or updating time. As i mentioned previous answer you can do that without looping. just make a temptable and update the data or inserted the data in temptable. After then using join update data.
0
Uday Suragani6y ago
Joginder,

Yes, I copied the Question content from Stack Overflow. But I added While at the end of the Question FYI.

Now I am working on performance issues. As per our client request, we should not use Cursor and While for looping records for better performance.

Example. If I want to insert/Update any bulk of records by checking some conditions, we are using cursor / While loops. can u suggest any new to avoid both of them?.
0
Joginder Banger6y ago
First of all your question is incomplete, share your complete information. i beleive you pickup this question from stackoverflow. I pickup also accepted answer data for you.
Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End
above code reference
https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor
0