loop through temp table w/o cursor

Experts say to avoid cursors at all costs.

How do you loop through a temp table w/o a cursor?

I need to delete certain records in my temp table if they don't meet a certain test.
LVL 4
g_johnsonAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
Here's a code snippet that uses a cursor to delete all the rows in all the user tables where screen_name = 'Gary'. Maybe you can adapt:

DECLARE csrName CURSOR FOR
   SELECT distinct so.Name
     FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id
   WHERE sc.name = 'Screen_Name'
   AND so.type = 'U'  -- user table only
   AND so.category = 1  

DECLARE @name NVARCHAR(128)
DECLARE @sql NVARCHAR(4000)

OPEN csrName
FETCH NEXT FROM csrName INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'Delete From ' + @name + N' where screen_name = ''Gary'''
    EXEC(@sql)
    FETCH NEXT FROM csrName INTO @name
END --WHILE
CLOSE csrName
DEALLOCATE csrName
0
 
dqmqConnect With a Mentor Commented:
> Experts say to avoid cursors at all costs.

That may be a bit of an overstatement.

But, set operations are what relational databases are designed for.  You can perfrom your delete to a subset of the table if you can describe the subset in a where clause:

Delete from YourTable where textcolumn <> '5' and numeric column <> 2

Of course, the where clause is usually much more complicated
0
 
johnclarke123Connect With a Mentor Commented:
DELETE FROM #MyTempTable
WHERE -- your condition: e.g. MyColumn <> 0 or whatever

If you can encapsulate the condition in a single statement (as opposed to having to do some procedural logic) you don't need a cursor.  This should be the case 99% of the time.  Cursors are really only needed where you want to do something quite complex to a row that can't fit in the format above.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
OmnibuzzCommented:
Can you elaborate on the certain test
with some info on the table structure?
0
 
Ved Prakash AgrawalConnect With a Mentor Database Consultant/Performance ArchitectCommented:
Hi,

you can use table variable which has Indentity column  and then insert the data into that table.

and use while loop on the basis of identity column .


0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
There is no need of delete of certain test because you are featching records on the basis of identity column which will give you a unique records.

0
 
g_johnsonAuthor Commented:
The delete if pretty complex (if you're me!   lol)

See this question
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21850838.html

Since I don't have a working answer yet to that question, I was going to try to take a different approach
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.