Solved

For each without temp table in T-SQL

Posted on 2010-08-30
8
397 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now