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?
Add a comment
|
5 Answers
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
answered Sep 7, 2012 at 18:50
LittleBobbyTables - Au Revoir
33k25 gold badges113 silver badges115 bronze badges
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
2 Comments
Satish
Do i need to create #TempTable first before running that query/
Mikael Eriksson
No you don't. If you want to fill a table that already exist with rows you need to use a different syntax.
In MySQL:
create table temp as select * from original_table
3 Comments
Jeson Martajaya
Modifying temp to #temp does not work. Error message: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'AS'.
techdude
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
Leif Neland
The tag "SQL Server" is only for Microsoft SQL, not other like Mysql or ORACLE:
Try:
exec('drop table #tab') -- you can add condition 'if table exists'
exec('select * into #tab from tab')
Comments
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
