• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

For each without temp table in T-SQL

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
PeteEngineer
Asked:
PeteEngineer
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
Rajkumar GsSoftware EngineerCommented:
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
 
PeteEngineerAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bhavesh ShahLead AnalysistCommented:
Hi...

why you want to go for that?

Please explain, might experts give u bettet solution
0
 
Rajkumar GsSoftware EngineerCommented:
Explain your exact requirement.

Raj
0
 
PeteEngineerAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

"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
 
cyberkiwiCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now