Solved

For each without temp table in T-SQL

Posted on 2010-08-30
8
424 Views
Last Modified: 2012-05-10
Is there any way that i can use a "For-Each" for each rows of a selected query ..without using a temp table in my T-SQL stored procedure?
0
Comment
Question by:PeteEngineer
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 125 total points
ID: 33564344
Use can use WHILE loop. See the code

Raj
CREATE TABLE #table
(	
	id int,
	name varchar(50)
)

insert into #table
	select 1, 'Name1' union
	select 2, 'Name2'


DECLARE @i INT
SET @i = 0

WHILE @i < 10
BEGIN
	print @i
	-- code here
	SELECT * FROM #table WHERE id = @i

	SET @i = @i + 1

END

GO

Open in new window

0
 
LVL 8

Author Comment

by:PeteEngineer
ID: 33564508
i dont want to use while loops .. if there are toooooo many names in the #table it take hell lot of memory ..

is there anyway that i can tweak this ?

using a foreach or cursor or something else?
0
 
LVL 7

Assisted Solution

by:rashmi_vaghela
rashmi_vaghela earned 125 total points
ID: 33564597
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 125 total points
ID: 33564743
Hi...

why you want to go for that?

Please explain, might experts give u bettet solution
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33564770
Explain your exact requirement.

Raj
0
 
LVL 8

Author Comment

by:PeteEngineer
ID: 33565120
yes need to iterated for all the sales item in my table.

But i can do it is very little number but what about i lakh items?

it takes hell lot of memory .. since it is putting the item into temp table and take it back

rather it can be done with a for each or cursor or something will it take the same amount of memory?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33565202

"yes need to iterated for all the sales item in my table."

you doing for update or insert?

try to make single query and avoid loop if possible.

if its 1 lakh rows, surely it going to slow.

if u can discuss more in detail then experts can help u to make single query.
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 125 total points
ID: 33566262
As Brichsoft has mentioned, for 100k items, best to do within a single query if you find the logic for it.
With SQL Server 2005+, analytical functions and CTE, a lot is possible.

> rather it can be done with a for each or cursor or something will it take the same amount of memory?
If you must use a cursor, a forward only, NON-static cursor will probably not take too much resources on the server.  But it will lock access for quite a while and MUST have a good index that it can travel on.  In fact, I normally favour while loops.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question