如何在临时表中插入SQL查询结果?

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?

我有一个SQL查询(SQL Server)，它生成报告，我想将准确的报告存储在temp表中，以便稍后使用它。现在的问题是，我是否需要先创建临时表，然后将SQL查询结果存储到它中，或者是否有任何方法可以动态地创建表和存储查询结果?

5 个解决方案

#1

102

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

#2

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

您可以使用select…成……创建并填充临时表，然后查询临时表以返回结果。

select *
into #TempTable
from YourTable

select *
from #TempTable

#3

In MySQL:

在MySQL中:

create table temp as select * from original_table

#4

Try:

试一试:

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

#5

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

你必须将这些数据保存到temparory表中然后你要查询到

Select EmployeeId,EmployeeName into #MyTempTable from Employee Where EmployeeId>101 order by EmployeeName

#1

102