Create a Temporary table within a SQL Stored Procedure

Revision: 20084

at November 6, 2009 11:17 by highlyevolved

Updated Code

CREATE TABLE #TableName(
ID int,
FieldName nVarChar(30) )

INSERT INTO #TableName (ID, FieldName)
SELECT 	ID, FieldName
FROM 	dbo.TableName
WHERE 	Type = 'Value'

-- Do some stuff with the table

drop table #TableName


-------------------------------------------------------

For Multiple Queries repeat the INSERT INTO statement

CREATE TABLE #TableName(
ID int,
FieldName nVarChar(30) )

INSERT INTO #TableName (ID, FieldName)
SELECT 	ID, FieldName
FROM 	dbo.TableName
WHERE 	Type = 'Value'

INSERT INTO #TableName (ID, FieldName)
SELECT 	ID, FieldName
FROM 	dbo.TableName
WHERE 	Type = 'Value'

-- Do some stuff with the table eg SELECT * From #TableName

drop table #TableName

Revision: 20083

at November 6, 2009 11:12 by highlyevolved

Initial Code

CREATE TABLE #TableName(
ID int,
FieldName nVarChar(30) )

INSERT INTO #TibetanYaks (ID, FieldName)
SELECT 	ID, FieldName
FROM 	dbo.TableName
WHERE 	Type = 'Value'

-- Do some stuff with the table

drop table #TableName


-------------------------------------------------------

For Multiple Queries repeat the INSERT INTO statement

CREATE TABLE #TableName(
ID int,
FieldName nVarChar(30) )

INSERT INTO #TibetanYaks (ID, FieldName)
SELECT 	ID, FieldName
FROM 	dbo.TableName
WHERE 	Type = 'Value'

INSERT INTO #TibetanYaks (ID, FieldName)
SELECT 	ID, FieldName
FROM 	dbo.TableName
WHERE 	Type = 'Value'

-- Do some stuff with the table eg SELECT * From #TableName

drop table #TableName

Initial URL

Initial Description

A Temp table is great for combining multiple SELECT statements in a Sproc and outputting as just one table.

Don't forget to Drop the table after the  SELECT * FROM #TempTableName at the end.

Initial Title

Create a Temporary table within a SQL Stored Procedure

Initial Tags

sql, table

Initial Language

SQL

Choose a language for easy browsing: