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!
