SQL TSQL从动态sql中选择到临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9534990/
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
TSQL select into Temp table from dynamic sql
提问by Patrick
This seems relatively simple, but apparently it's not.
这看起来相对简单，但显然并非如此。
I need to create a temp table based on an existing table via the select into syntax:
我需要通过 select into 语法基于现有表创建一个临时表：
SELECT * INTO #TEMPTABLE FROM EXISTING_TABLE
The problem is, the existing table name is accepted via a parameter...
问题是，现有的表名是通过参数接受的...
I can get the table's data via:
我可以通过以下方式获取表的数据：
execute ('SELECT * FROM ' + @tableName)
but how do I marry the two so that I can put the results from the execute directly into the temp table.
但是我如何将两者结合起来，以便我可以将执行的结果直接放入临时表中。
The columns for each table that this is going to be used for are not the same so building the temp table before getting the data is not practical.
这将用于每个表的列不相同，因此在获取数据之前构建临时表是不切实际的。
I'm open to any suggestions except using a global temp table.
除了使用全局临时表之外，我愿意接受任何建议。
Update:
更新：
This is completely ridiculous, BUT my reservations with the global temp table is that this is a multi user platform lends itself to issues if the table will linger for long periods of time...
这完全是荒谬的，但我对全局临时表的保留意见是，这是一个多用户平台，如果表会长时间逗留，它会导致问题......
Sooo.. just to get past this part I've started by using the execute to generate a global temp table.
Sooo .. 只是为了通过这部分我已经开始使用执行来生成一个全局临时表。
execute('select * into ##globalDynamicFormTable from ' + @tsFormTable)
I then use the global temp table to load the local temp table:
然后我使用全局临时表加载本地临时表：
select * into #tempTable from ##globalDynamicFormTable
I then drop the global table.
然后我删除全局表。
drop table ##globalDynamicFormTable
this is dirty and I don't like it, but for the time being, until i get a better solution, its going to have to work.
这很脏，我不喜欢它，但就目前而言，在我找到更好的解决方案之前，它必须起作用。
In the End:
到底：
I guess there is no way to get around it.
我想没有办法绕过它。
The best answer appears to be either;
最好的答案似乎是；
Create a viewin the execute command and use that to load the local temp table in the stored procedure.
在执行命令中创建一个视图并使用它来加载存储过程中的本地临时表。
Create a global temp tablein the execute command and use that to load the local temp table.
在执行命令中创建一个全局临时表并使用它来加载本地临时表。
With that said i'll probably just stick with the global temp table because creating and dropping views is audited in my organization, and I'm sure they are going to question that if it starts happening all the time.
话虽如此，我可能只会坚持使用全局临时表，因为在我的组织中对创建和删除视图进行了审核，而且我确信他们会质疑它是否一直发生。
Thanks!
谢谢！
回答by Kaf
A working example.
一个工作示例。
DECLARE @TableName AS VARCHAR(100)
SELECT @TableName = 'YourTableName'
EXECUTE ('SELECT * INTO #TEMP FROM ' + @TableName +'; SELECT * FROM #TEMP;')
Second solution with accessible temp table
具有可访问临时表的第二个解决方案
DECLARE @TableName AS VARCHAR(100)
SELECT @TableName = 'YOUR_TABLE_NAME'
EXECUTE ('CREATE VIEW vTemp AS
SELECT *
FROM ' + @TableName)
SELECT * INTO #TEMP FROM vTemp
--DROP THE VIEW HERE
DROP VIEW vTemp
/*START USING TEMP TABLE
************************/
--EX:
SELECT * FROM #TEMP
--DROP YOUR TEMP TABLE HERE
DROP TABLE #TEMP
回答by Hayk
declare @sql varchar(100);
declare @tablename as varchar(100);
select @tablename = 'your_table_name';
create table #tmp
(col1 int, col2 int, col3 int);
set @sql = 'select aa, bb, cc from ' + @tablename;
insert into #tmp(col1, col2, col3) exec( @sql );
select * from #tmp;
回答by Donovan Paul Froon
How I did it with a pivot in dynamic sql (#AccPurch was created prior to this)
我是如何使用动态 sql 中的枢轴完成的（#AccPurch 是在此之前创建的）
DECLARE @sql AS nvarchar(MAX)
declare @Month Nvarchar(1000)
--DROP TABLE #temp
select distinct YYYYMM into #temp from #AccPurch AS ap
SELECT @Month = COALESCE(@Month, '') + '[' + CAST(YYYYMM AS VarChar(8)) + '],' FROM #temp
SELECT @Month= LEFT(@Month,len(@Month)-1)
SET @sql = N'SELECT UserID, '+ @Month + N' into ##final_Donovan_12345 FROM (
Select ap.AccPurch ,
ap.YYYYMM ,
ap.UserID ,
ap.AccountNumber
FROM #AccPurch AS ap
) p
Pivot (SUM(AccPurch) FOR YYYYMM IN ('+@Month+ N')) as pvt'
EXEC sp_executesql @sql
Select * INTO #final From ##final_Donovan_12345
DROP TABLE ##final_Donovan_12345
Select * From #final AS f
回答by Diego
Take a look at OPENROWSET, and do something like:
看看OPENROWSET，然后执行以下操作：
SELECT * INTO #TEMPTABLE FROM OPENROWSET('SQLNCLI'
, 'Server=(local)\SQL2008;Trusted_Connection=yes;',
'SELECT * FROM ' + @tableName)
