Solved

For each without temp table in T-SQL

Posted on 2010-08-30
8
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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