Reader Level:

Using Temp Table in SQL Server And Adding Columns Dynamically

By Amit Choudhary on Jul 16, 2011
In this article we'll see how to use the Temporary tables, what are the necessary step to be taken while using them and moreover sometimes it's required to add column in Temporary tables dynamically.

While creating a stored procedure it's often necessary to create temporary tables to hold temporary operational data. To do that there are options available in SQL Server you can use; Cursors, #Temp tables or Table variables. In this article we'll see how to use the Temporary tables and what are the necessary steps to be taken while using them and moreover sometimes it's required to add a column in Temporary tables dynamically. I hope this article will help you achieving all the basics operations with Temporary tables.

Declaring Temporary Table:

CREATE TABLE #Mytemp(Col1 nvarchar(100), Col2 int)

Now before using this statement in your SQL always place a check if table already exists in TempDB. (Temporary tables are getting created in TempDB in SQLServer.) Drop that table first otherwise you'll see error when you run the SQL. Well its suggested you should Drop all the temporary objects you've created in your SQL for cleanup.

 FROM tempdb.dbo.sysobjects
 WHERE ID = OBJECT_ID(N'tempdb..#Mytemp')

Adding records into #Temp table:

 from [SomeTable]

Note: Always create the temp table structure based on the query that inserts the data in #Temp table.

Looping through the Records:

Experts don't recommend using Cursors due to slow performance. So #Temp tables can be replacements to Cursors. Let see how to iterate through rows in temp table.

DECLARE @Var1 nvarchar(100)
WHILE (SELECT COUNT(*) from #Mytemp) >0
 SELECT @Var1 = Col1 from #Mytemp
--Do something here

  Delete #Mytemp Where  @Var1 = Col1

These are the basics things that required to deal with temp table variables.

Now let's do some interesting operation on #Temp tables.

Adding Columns in #Temp table dynamically:

Temp tables allow changes to their structure when required after creation.

DECLARE @ColName nvarchar(100)
DECLARE @DynamicSQL nvarchar(250)
SET @ColName='newColumn'
SET @DynamicSQL = 'ALTER TABLE #Mytemp ADD ['+ CAST(@ColName AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'


Adding Identity Column into #Temp Table:

CREATE TABLE #tmp(ID INT IDENTITY(1,1), Col1 nvarchar(100), Col2 int)

Or you can later add it by using the ALTER statement


SQL is fun!!

Amit Choudhary
Amit Choudhary

I'm a blogger, contributor, speaker more or less a community guy. If you have any topics that you want to have it available as blog/video/answer let me know, write me a message on c-sharpcorner or email me amit_85i[AT]ho... Read more

Personal Blog: