Loop Statements without cursor and while in SQL

Question

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

Joginder Banger · 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

Joginder Banger · Answer

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.

Uday Suragani · Answer

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?.

Joginder Banger · Answer

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

Loop Statements without cursor and while in SQL

Answers (4)