Introduction
In this article, we'll see how to use the Temporary tables and what are the necessary steps to be taken while using them moreover sometimes it's required to add a column in Temporary tables dynamically. I hope this article will help you achieve all the basic operations with Temporary tables in SQL Server.
How to Create temporary tables in SQL Server?
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.
Declaring Temporary Table
CREATE TABLE #Mytemp(Col1 nvarchar(100), Col2 int)
Now before using this statement in your SQL always place check if the table already exists in TempDB. (Temporary tables are getting created in TempDB in SQLServer.) Drop that table first otherwise you'll see an error when you run the SQL. Well, it's suggested you should Drop all the temporary objects you've created in your SQL for cleanup.
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#Mytemp')
)
BEGIN
DROP TABLE #Mytemp
END
Adding records into #Temp table:
INSERT INTO Select *
from [SomeTable]
Note
Always create the temp table structure based on the query that inserts the data in the #Temp table.
Looping through the Records
Experts don't recommend using Cursors due to slow performance. So #Temp tables can be replacements for Cursors. Let's see how to iterate through rows in the temp table.
DECLARE @Var1 nvarchar(100)
WHILE (SELECT COUNT(*) from #Mytemp) >0
BEGIN
SELECT @Var1 = Col1 from #Mytemp
--Do something here
EXEC(@DynamicSQL)
--
Delete #Mytemp Where @Var1 = Col1
END
These are the basic things that are required to deal with temp table variables.
Now let's do some interesting operations 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'
EXEC(@DynamicSQL)
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
ALTER TABLE #Temp ADD AutoID INT IDENTITY(1,1);
Conclusion
In this article, we'll see how to use the Temporary tables and what are the necessary steps to be taken while using them in SQL Server.