Thursday, June 9, 2011

Dropping Local Temp Table

A collegue needed to create a local temp table in his work to insert records. When executed the script to create and insert records after first try he kept getting error message stating that 'There is already object named #TestTable in the database. I told him that he should clean up the Temp tables at the end when he is done with them and also to be safe in the beginning of the code to check and drop if the table exists. Here is an example.

-- CHECK AND DROP TEMP TABLE IF EXISTS
IF OBJECT_ID ('Tempdb.dbo.#TestTempTable') IS NOT NULL
BEGIN
DROP TABLE #TestTempTable
END
-- CREATE TEMP TABLE
CREATE TABLE #TestTempTable (
RowID tinyint not null,
City varchar(50) not null
)
-- POPULATE WITH DATA
INSERT INTO #TestTempTable
SELECT 1,'London'
UNION
SELECT 2, 'Chicago'
UNION
SELECT 3, 'Paris'
/***
MORE CODE HERE
***/
SELECT * FROM #TestTempTable
-- WHEN DONE CLEAN UP THE TEMP TABLE
DROP TABLE #TestTempTable
Enhanced by Zemanta

No comments:

Post a Comment