SQL 使用查询将数据插入临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20107827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Data Into Temp Table with Query
提问by scapegoat17
I have an existing query that outputs current data, and I would like to insert it into a Temp table, but am having some issues doing so. Would anybody have some insight on how to do this?
我有一个输出当前数据的现有查询，我想将其插入到临时表中，但这样做时遇到了一些问题。有人会对如何做到这一点有一些见解吗？
Here is an example
这是一个例子
SELECT *
FROM (SELECT Received,
Total,
Answer,
( CASE
WHEN application LIKE '%STUFF%' THEN 'MORESTUFF'
END ) AS application
FROM FirstTable
WHERE Recieved = 1
AND application = 'MORESTUFF'
GROUP BY CASE
WHEN application LIKE '%STUFF%' THEN 'MORESTUFF'
END) data
WHERE application LIKE isNull('%MORESTUFF%', '%')
This seems to output my data currently the way that i need it to, but I would like to pass it into a Temp Table. My problem is that I am pretty new to SQL Queries and have not been able to find a way to do so. Or if it is even possible. If it is not possible, is there a better way to get the data that i am looking for WHERE application LIKE isNull('%MORESTUFF%','%')into a temp table?
这似乎当前以我需要的方式输出我的数据，但我想将其传递到临时表中。我的问题是我对 SQL 查询很陌生，并且无法找到这样做的方法。或者，如果它甚至可能。如果不可能，是否有更好的方法将我正在查找的数据WHERE application LIKE isNull('%MORESTUFF%','%')放入临时表？
回答by Yosi Dahari
SELECT *
INTO #Temp
FROM
(SELECT
Received,
Total,
Answer,
(CASE WHEN application LIKE '%STUFF%' THEN 'MORESTUFF' END) AS application
FROM
FirstTable
WHERE
Recieved = 1 AND
application = 'MORESTUFF'
GROUP BY
CASE WHEN application LIKE '%STUFF%' THEN 'MORESTUFF' END) data
WHERE
application LIKE
isNull(
'%MORESTUFF%',
'%')
回答by Shaun Luttin
SQL Server R2 2008 needs the ASclause as follows:
SQL Server R2 2008 需要AS如下子句：
SELECT *
INTO #temp
FROM (
SELECT col1, col2
FROM table1
) AS x
The query failed without the AS xat the end.
查询失败，没有AS x结尾。
EDIT编辑
It's also needed when using SS2016, had to add as tto the end.
SS2016的时候也需要，只好加到as t最后。
Select * into #result from (SELECT * FROM #temp where [id] = @id) as t //<-- as t
回答by Yuriy Galanter
Fastest way to do this is using "SELECT INTO" command e.g.
最快的方法是使用“SELECT INTO”命令，例如
SELECT * INTO #TempTableName
FROM....
This will create a new table, you don't have to create it in advance.
这将创建一个新表，您不必提前创建它。
回答by theteague
Personally, I needed a little hand holding figuring out how to use this and it is really, awesome.
就个人而言，我需要一只小手来弄清楚如何使用它，这真的非常棒。
IF(OBJECT_ID('tempdb..#TEMP') IS NOT NULL) BEGIN DROP TABLE #TEMP END
SELECT *
INTO #TEMP
FROM (
The query you want to use many times
) AS X
SELECT * FROM #TEMP WHERE THIS = THAT
SELECT * FROM #TEMP WHERE THIS <> THAT
SELECT COL1,COL3 FROM #TEMP WHERE THIS > THAT
DROP TABLE #TEMP
回答by wvdz
You can do that like this:
你可以这样做：
INSERT INTO myTable (colum1, column2)
SELECT column1, column2 FROM OtherTable;
Just make sure the columns are matching, both in number as in datatype.
只需确保列匹配，无论是数量还是数据类型。
回答by Alok Sharma
Try this:
尝试这个：
SELECT *
INTO #Temp
FROM
(select * from tblorders where busidate ='2016-11-24' and locationID=12
) as X
Please use alias with x so it will not failed the script and result.
请在 x 中使用别名，以免脚本和结果失败。
回答by Saqib A. Azhar
SELECT * INTO #TempTable
FROM SampleTable
WHERE...
SELECT * FROM #TempTable
DROP TABLE #TempTable
回答by Luiz Henrique Lima
This is possible. Try this way:
这个有可能。试试这个方法：
Create Global Temporary Table
BossaDoSamba
On Commit Preserve Rows
As
select ArtistName, sum(Songs) As NumberOfSongs
from Spotfy
where ArtistName = 'BossaDoSamba'
group by ArtistName;
