Advanced Search

Create a Temporary table within a SQL Stored Procedure

/ Published in: SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE TABLE #TableName(
  2. ID INT,
  3. FieldName nVarChar(30) )
  4.  
  5. INSERT INTO #TableName (ID, FieldName)
  6. SELECT 	ID, FieldName
  7. FROM 	dbo.TableName
  8. WHERE 	TYPE = 'Value'
  9.  
  10. -- Do some stuff with the table
  11.  
  12. DROP TABLE #TableName
  13.  
  14.  
  15. -------------------------------------------------------
  16.  
  17. FOR Multiple Queries repeat the INSERT INTO statement
  18.  
  19. CREATE TABLE #TableName(
  20. ID INT,
  21. FieldName nVarChar(30) )
  22.  
  23. INSERT INTO #TableName (ID, FieldName)
  24. SELECT 	ID, FieldName
  25. FROM 	dbo.TableName
  26. WHERE 	TYPE = 'Value'
  27.  
  28. INSERT INTO #TableName (ID, FieldName)
  29. SELECT 	ID, FieldName
  30. FROM 	dbo.TableName
  31. WHERE 	TYPE = 'Value'
  32.  
  33. -- Do some stuff with the table eg SELECT * From #TableName
  34.  
  35. DROP TABLE #TableName

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.