?
Solved

Looping through the records

Posted on 2013-01-27
4
Medium Priority
?
272 Views
Last Modified: 2013-01-31
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
0
Comment
Question by:ravichand-sql
[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
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38825380
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
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38825389
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
 
LVL 6

Accepted Solution

by:
esolve earned 1500 total points
ID: 38827067
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
 

Author Comment

by:ravichand-sql
ID: 38841614
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

770 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