SQL SELECT INTO #temp .. FROM [Query1] OR [Query2]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14776036/
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
SELECT INTO #temp.. FROM [Query1] OR [Query2]
提问by Janine Rawnsley
Added clarification: My code is run inside a Stored Procedure.
添加说明：我的代码在存储过程中运行。
I've run into this issue multiple times over the years so thought I'd bite the bullet and finally ask, even though I think I might be missing a simple solution.
多年来我多次遇到这个问题，所以我想我会咬紧牙关最后问，即使我认为我可能会错过一个简单的解决方案。
Oftentimes is the case I like to use
通常情况下我喜欢使用
SELECT * INTO #Temp FROM ...
.. Because I find it generally performs better than any other means of temp table generation when used on small data sets.
.. 因为我发现在小数据集上使用时，它的性能通常比任何其他临时表生成方法都要好。
But sometimes I'll have a parameterised query which can run in more than one "mode", thus need my statement to be able to populate #Temp from two completely different sources depending on this mode, e.g.
但有时我会有一个参数化查询，它可以在多个“模式”下运行，因此需要我的语句能够根据这种模式从两个完全不同的来源填充#Temp，例如
Option 1
选项1
IF @RoleID > -1
SELECT PrivilegeID INTO dbo.#Privs FROM Users.tblRolePrivilegeLink WHERE RoleID = @RoleID
Option 2
选项 2
IF @UserID > -1
SELECT PrivilegeID INTO dbo.#Privs FROM dbo.fn_UserPrivileges(@UserID)
In this example I'm trying to get a set of privileges for either a role or a user, one straight from a table, another from a function.
在这个例子中，我试图为角色或用户获取一组权限，一个直接来自表，另一个来自函数。
I know I could use the regular INSERT INTO.. logic but I'd be really interested to see if there's an actual solution for doing it this way. Maybe with CTE's or somesuch.
我知道我可以使用常规的 INSERT INTO .. 逻辑，但我真的很想看看是否有这样做的实际解决方案。也许与 CTE 或类似。
Solution
解决方案
-- Get distinct privileges
SELECT PrivilegeID
INTO dbo.#Privs
FROM
(
SELECT PrivilegeID FROM Users.tblRolePrivilegeLink WHERE RoleID > -1 AND RoleID = @RoleID
UNION
SELECT PrivilegeID FROM dbo.fn_UserPrivileges(@UserID) WHERE @UserID > -1
) AS T
采纳答案by paul
SELECT *
INTO dbo.#Privs
from (
SELECT PrivilegeID
FROM Users.tblRolePrivilegeLink
WHERE RoleID = @RoleID and @RoleID > -1
UNION
SELECT PrivilegeID
FROM dbo.fn_UserPrivileges(@UserID)
WHERE @UserID > 1)
回答by Gordon Linoff
You can do thisby separating the staetments using "go":
您可以通过使用“go”分隔语句来做到这一点：
if 1=0 select 1 as v into #temp;
go
if 1=1 select 2 as v into #temp;
go
select * from #temp
This code assumes that #tempdoes not exist initially.
此代码假定#temp最初不存在。
