77

I have a SQL query (SQL Server) and it generate reports, I want to store that exact report in temp table so I can play with it later. Now question is do I need to create temp table first and then store SQL query result into it, or is there any way to dynamically create table and store query result?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Satish
  • 16,544
  • 29
  • 93
  • 149

5 Answers5

131

Look at SELECT INTO. This will create a new table for you, which can be temporary if you want by prefixing the table name with a pound sign (#).

For example, you can do:

SELECT * 
INTO #YourTempTable
FROM YourReportQuery
LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
  • 6
    Borrowed from another thread, `#yourtemptable` does not need to be created first. – jfa Mar 30 '17 at 16:35
  • 5
    This won't work and will throw error as Incorrect syntax near the keyword 'SELECT' if YourReportQuery is query and not database table. – Roshan Dec 07 '17 at 10:05
26

You can use select ... into ... to create and populate a temp table and then query the temp table to return the result.

select *
into #TempTable
from YourTable

select *
from #TempTable
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
4

In MySQL: 

create table temp as select * from original_table
Phil3992
  • 1,059
  • 6
  • 21
  • 45
Hunter
  • 820
  • 8
  • 19
  • Modifying temp to #temp does not work. Error message: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'AS'. – Jeson Martajaya Apr 04 '14 at 18:06
  • This is a structure that works in MySQL and possibly others. Op didn't specify which server type is being used, but since he called it SQL Server, I would guess it is Microsoft SQL Server. By the way, for others that need to use this for MySQL, @Hunter's answer would create a new table. To create a new temporary table, you need to add the TEMPORARY keyword like this: CREATE TEMPORARY TABLE temptbl AS SELECT ... FROM originaltbl – techdude Aug 06 '14 at 17:20
  • The tag "SQL Server" is only for Microsoft SQL, not other like Mysql or ORACLE: – Leif Neland Feb 07 '19 at 14:43
3

Try:

exec('drop table #tab') -- you can add condition 'if table exists'
exec('select * into #tab from tab')
Robert
  • 25,425
  • 8
  • 67
  • 81
1

Suppose your existing reporting query is 

Select EmployeeId,EmployeeName 
from Employee 
Where EmployeeId>101 order by EmployeeName

and you have to save this data into temparory table then you query goes to 

Select EmployeeId,EmployeeName 
into #MyTempTable 
from Employee 
Where EmployeeId>101 order by EmployeeName
Saghir A. Khatri
  • 3,429
  • 6
  • 45
  • 76
Roshan
  • 873
  • 12
  • 33