loop through temp table w/o cursor

Posted on 2006-05-15
Last Modified: 2012-05-05
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.
Question by:g_johnson
    LVL 42

    Assisted Solution

    > 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
    LVL 4

    Assisted Solution

    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.
    LVL 2

    Expert Comment

    Can you elaborate on the certain test
    with some info on the table structure?
    LVL 11

    Assisted Solution

    by:Ved Prakash Agrawal

    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 .

    LVL 11

    Expert Comment

    by:Ved Prakash Agrawal
    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.

    LVL 4

    Author Comment

    The delete if pretty complex (if you're me!   lol)

    See this question

    Since I don't have a working answer yet to that question, I was going to try to take a different approach
    LVL 42

    Accepted Solution

    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:

       SELECT distinct so.Name
         FROM syscolumns sc INNER JOIN sysobjects so ON =
       WHERE = '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
        SET @sql = N'Delete From ' + @name + N' where screen_name = ''Gary'''
        FETCH NEXT FROM csrName INTO @name
    CLOSE csrName
    DEALLOCATE csrName

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now