Solved

Looping through the records

Posted on 2013-01-27
4
270 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 92

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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to restore or keep sql2000  backups useful... 2 37
sql 2016 data tools breakdown.. 1 36
Linked Server - SP with Param to VIew 7 29
t-sql left join 2 31
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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