Solved

For each without temp table in T-SQL

Posted on 2010-08-30
8
416 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

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.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

821 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