How SQL query result insert in temp table?

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?

290458 次浏览

小开

最佳答案

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

小开

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

小开

Try:

exec('drop table #tab') -- you can add condition 'if table exists'
exec('select * into #tab from tab')

小开

In MySQL:

create table temp as select * from original_table

小开

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