Posted By

highlyevolved on 11/06/09


Tagged

sql table sproc stored procedure temp queries Mulitple


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

highlyevolved
Tyster
johnw


Create a Temporary table within a SQL Stored Procedure


 / Published in: SQL
 

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.

  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  

You need to login to post a comment.