Avoiding Cursors and Loops for Append Operations

Most of the times, to do basic append operations in SQL; the very first thing comes in mind is the CURSOR and a LOOP.

For example, if I want to print a comma seperated list of columns in any given table; i would quickly think of a cursor with a loop assigning the comma seperated column list to a variable.

BUT, there is pretty simpler, smarter, and quicker work around to do appends like this:

TRADITIONAL CURSOR WITH WHILE LOOP SOLUTION FOR APPENDS:

create table #mytable (column_a varchar(2), column_b varchar(2), column_c varchar(3))

declare cursor_mytable cursor

for select c.name

from tempdb.dbo.sysobjects o

join tempdb.dbo.syscolumns c on c.id=o.id

where o.id=object_id('tempdb.dbo.#mytable')

and o.type = 'U'

declare @v varchar(100), @v2 varchar(800)

set @v2 =''

open cursor_mytable

fetch cursor_mytable into @v

while @@fetch_status = 0

begin

if @v2 =''

begin

set @v2 =@v

end

else

begin

set @v2=@v2+','+@v

end

fetch cursor_mytable into @v

end

close cursor_mytable

deallocate cursor_mytable

drop table #mytable

select @v2

SIMPLE CURSOR & LOOPLESS, YET SMARTER SOLUTION FOR APPENDS:

create table #mytable (column_a varchar(2), column_b varchar(2), column_c varchar(3))

declare @v varchar(100), @v2 varchar(800), @tbl sysname

select @v2 = '', @v = ''

select @v = c.name , @v2 = @v2+','+@v

from tempdb.dbo.syscolumns c

INNER JOIN tempdb.dbo.sysobjects o on c.id = o.id

where o.id=object_id('tempdb.dbo.#mytable')

and o.type = 'U'

drop table #mytable

select substring(@v2,2,len(@v2))

The output of both code snippets above is the same but simplicity is obvious in the latter one!