CODEWITHSUNDEEP
sql-servertemp-tables
Walid
Walid

Reputation: 197

Looping & Renaming #Temp table columns

I've got a #TempTble which looks like this:

Col1,Col2,5,8,19,....

Also, I have another table with description that matches the keys in #TempTble:

key   Descr    
===   ====    
5     Descr1    
8     Descr2    
19    Descr3

Which is the best way to loop through the #TempTble and rename its columns with the matching descriptions so it looks like this:

Col1,Col2,Descr1,Descr2,Descr3,...

Thanks in advance.

Upvotes: 1

Views: 5842

Answers (2)

Martin Smith
Martin Smith

Reputation: 453028

IF object_id('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

declare @map table ([key] sysname,Descr sysname)

INSERT INTO @map
select 5,'Descr1' UNION ALL
select 8,'Descr2' UNION ALL    
select 19,'Descr3'

create table #Temp ([Col1] int,[Col2] int,[5] int,[8] int,[19] int)

DECLARE @name nvarchar(1035), @descr sysname;

DECLARE ColumnCursor CURSOR 
LOCAL FORWARD_ONLY STATIC READ_ONLY TYPE_WARNING
FOR SELECT 'tempdb..#Temp.' + QUOTENAME(name), Descr
FROM tempdb.sys.columns 
JOIN @map m ON m.[key]=name
where object_id=object_id('tempdb..#Temp');

OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @name, @descr;
WHILE @@FETCH_STATUS = 0
BEGIN;
 EXECUTE tempdb..sp_rename @name, @descr,'COLUMN';
FETCH NEXT FROM ColumnCursor INTO @name, @descr;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

SELECT * FROM #Temp

Upvotes: 3

Madhivanan
Madhivanan

Reputation: 13700

This is a bad design One option is to run this code;copy the result and run it again

select 'exec tempdb..sp_rename ''#temp.['+t1.name+']'','''+t2.descrip+'''' 
from tempdb..syscolumns as t1 inner join mytable as t2 on t1.name=t2.[key] 
where id=OBJECT_ID('tempdb..#temp') and t1.name like '[0-9]%'

Upvotes: 0

Related Questions