Solved

For each without temp table in T-SQL

Posted on 2010-08-30
8
436 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 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