Looping through the records

Hello,

I have lots of records in my table and I want to perform looping on the table. Using CURSORS would is the option I know. Is there any other way to perform looping?? CURSORS have performance issues, right?

Please help!!

Thanks in advance
ravichand-sqlAsked:
Who is Participating?
 
esolveConnect With a Mentor Software Development ManagerCommented:
DECLARE @TmpTable TABLE (ID INT IDENTITY(1,1), Name VARCHAR(50))
INSERT INTO @TmpTable (Name) values ('James')
INSERT INTO @TmpTable (Name) values ('Pete')
INSERT INTO @TmpTable (Name) values ('Sarah')
INSERT INTO @TmpTable (Name) values ('Carina')
INSERT INTO @TmpTable (Name) values ('Shane')

--select * from @TmpTable

DECLARE @IteratorID INT
SET @IteratorID = 0

WHILE @IteratorID IS NOT NULL
BEGIN
      --SELECT ROW TO WORK WITH
      SET @IteratorID = (SELECT TOP 1 ID FROM @TmpTable)
      
      IF(@IteratorID IS NOT NULL)
      BEGIN
      ------------
            --DO WHAT YOU WANT WITH THIS ROW
            DECLARE @Name NVARCHAR(50)
            SET @Name = (SELECT Name FROM @TmpTable WHERE ID = @IteratorID)
            SELECT @Name
      ------------
      END
      
      --IMPORTANT: DELETE ROW TO ENSURE YOU'RE DONE WITH IT AND PREVENT STACK OVERFLOW
      DELETE FROM @TmpTable WHERE ID = @IteratorID
END
0
 
Patrick MatthewsCommented:
What is it that you are really trying to do?  "Looping on the table" does not tell us what you really need: are you doing an update?  A select?  Something else?

In any event, cursors should be avoided if what you need to do can be accomplished in a set-based operation via standard SQL statements.
0
 
Jerry MillerCommented:
You can try a While loop, but many times looping is not necessary. If you post exactly what you are trying to accomplish, maybe someone can give you a solution with better performance than a loop.

http://msdn.microsoft.com/en-us/library/ms178642%28v=sql.100%29.aspx
0
 
ravichand-sqlAuthor Commented:
DECLARE @TmpTable TABLE (ID INT IDENTITY(1,1), Name VARCHAR(50))
INSERT INTO @TmpTable (Name) values ('James')
INSERT INTO @TmpTable (Name) values ('Pete')
INSERT INTO @TmpTable (Name) values ('Sarah')
INSERT INTO @TmpTable (Name) values ('Carina')
INSERT INTO @TmpTable (Name) values ('Shane')

Instead of inserting data using INSERT statement can I write a SELECT query for bulk insert ??

Like,

SELECT name into @TmpTable  FROM tblSomeName  WHERE condition
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.