Alex Rivera | Logout

Is there a way to loop through a table variable in TSQL without using a cursor?

Asked 2008-09-15T07:18:51.347
312

Let's say I have the following simple table variable:

declare @databases table
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases

Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?

sql-server t-sql loops
Edit
Report

3 Answers

156

Just a quick note, if you are using SQL Server (2008 and above), the examples that have:

While (Select Count(*) From #Temp) > 0

Would be better served with 

While EXISTS(SELECT * From #Temp)

The Count will have to touch every single row in the table, the EXISTS only needs to touch the first one.

answered 2008-09-15T18:12:36.767
4

You can use a while loop:

While (Select Count(*) From #TempTable) > 0
Begin
    Insert Into @Databases...

    Delete From #TempTable Where x = x
End
answered 2008-09-15T07:38:40.100
2

I'm going to provide the set-based solution.

insert  @databases (DatabaseID, Name, Server)
select DatabaseID, Name, Server 
From ... (Use whatever query you would have used in the loop or cursor)

This is far faster than any looping techique and is easier to write and maintain.

answered 2010-01-25T19:32:32.833

Your Answer