Add column to temp table

10 Apr 2009 Leave a Comment

by Ashish Mahajan in Database, SQL Server Tags: SQL Server, Temp Tables

Dear Friends,

In this post, I will show you how to find if a column already exists in a temporary table and if not add that column in temporary table.

First create our temporary table and also add some columns in it.

IF OBJECT_ID(‘tempdb..#tempTable’) IS NOT NULL
  DROP TABLE #tempTable

CREATE TABLE #tempTable
(
  FieldName          VARCHAR(50),
  FieldDescription  VARCHAR(100),
  FieldData             VARCHAR(5000)
)
Now suppose you have a table tblMenus with a column strMenuName and you want to add one column in #tempTable for each row in tblMenus. Here is the way to do it:

DECLARE @strMenu AS VARCHAR(30)
DECLARE @sql           AS VARCHAR(1000)

DECLARE cur_Menus CURSOR
FOR SELECT strMenuName
    FROM dbo.tblMenus

OPEN cur_Menus
  FETCH NEXT FROM cur_Menus INTO @strMenu
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @sql = ‘IF NOT EXISTS (SELECT ”X” FROM TEMPDB.SYS.COLUMNS WHERE [OBJECT_ID] = OBJECT_ID(”tempdb..#tempTable”) AND [NAME] = ”’ + @strMenu + ”’)
                   BEGIN
                     ALTER TABLE #tempTable ADD [' + @strMenu  + '] VARCHAR(30)
                   END’
   
    EXEC(@sql)
   
    FETCH NEXT FROM cur_Menus INTO @strMenu
  END
 
CLOSE cur_Menus
DEALLOCATE cur_Menus

You Now have all the row as column in temp table.

and of course never forget to free our temporary resources.

IF OBJECT_ID(‘tempdb..#tempTable’) IS NOT NULL
  DROP TABLE #tempTable

Thanks,

Ashish

Advertisement