blocks|key|1419722|text|如果要通过在动态查询中修改动态查询来访问动态查询外部的临时表，可以做的一件事是在动态查询之外声明它。说|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1419723|CREATE+TABLE+#InvRcpt+(SKU+nvarchar(25))
declare+@qry+nvarchar(500)
set+@qry+=+'alter+TABLE+#InvRcpt+add+'
++++%2B+@Start+%2B+'+int,'+
++++%2B+@columnsRcpt+%2B',+
++++++Vendu+int,+
++++++[Retrait+d''inventaire]+int,+
++++++Ajout+int,+
++++++Solde+int,+'+
++++++%2B+@End+%2B+'+int'
Exec(@qry)
select+*+from+#InvRcpt|code-block|syntax|javascript|1419724|然后，您应该获得输出，并在外部使用temp表。|1419725|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

One thing you can do, if you want to access the temporary table to access outside the dynamic query by modifying it inside the dynamic query is to declare it outside the dynamic query. Say

<pre><code>CREATE TABLE #InvRcpt (SKU nvarchar(25))
declare @qry nvarchar(500)
set @qry = 'alter TABLE #InvRcpt add '
 + @Start + ' int,' 
 + @columnsRcpt +', 
 Vendu int, 
 [Retrait d''inventaire] int, 
 Ajout int, 
 Solde int, ' 
 + @End + ' int'
Exec(@qry)
select * from #InvRcpt
</code></pre>

Then you should get the output and also use the temp table outside.

blocks|key|1477877|text|假设您的环境使用分号(;)作为命令终止符，请尝试以下操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1477878|@qry+=+'CREATE+TABLE+#InvRcpt+(SKU+nvarchar(25),+'
++++++++%2B+@Start+%2B+'+int'+%2B+@columnsRcpt+%2B',+Vendu+int,+[Retrait+d''inventaire]+int
++++++++,+Ajout+int,+Solde+int,+'+%2B+@End+%2B+'+int)'
++++++++%2B+';'+
++++++++%2B+'select+*+from+#InvRcpt;'

Exec(@qry)|code-block|syntax|javascript|1477879|正如Sean先前的评论所指出的，您需要在同一个批中调用SELECT语句和CREATE，以便能够获得结果。|1477880|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Assuming your environment uses the semi-colon (;) as command terminator, try the following

<pre><code>@qry = 'CREATE TABLE #InvRcpt (SKU nvarchar(25), '
 + @Start + ' int' + @columnsRcpt +', Vendu int, [Retrait d''inventaire] int
 , Ajout int, Solde int, ' + @End + ' int)'
 + ';' 
 + 'select * from #InvRcpt;'

Exec(@qry)
</code></pre>

As indicated by the previous comment by Sean Lange, you would need to invoke the SELECT statement in the same batch with the CREATE to be able to get the results.

blocks|key|1477928|text|在EXEC+(@qry)之外创建#InvRcpt，方法如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1477929|IF+OBJECT_ID('tempdb..##InvRcpt1')+IS+NULL
BEGIN
EXEC+('CREATE+TABLE+#InvRcpt1+(SKU+nvarchar(25),+'+%2B+@Start+%2B+'+int'+%2B+@columnsRcpt+%2B+',+
++++++++++Vendu+int,+
++++++++++[Retrait+d''inventaire]+int,+
++++++++++Ajout+int,+
++++++++++Solde+int,+'+%2B+@End+%2B+'+int)')

END

SELECT+*
INTO+#InvRcpt
FROM+##InvRcpt1

SELECT+*
FROM+#InvRcpt|code-block|syntax|javascript|1477930|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

CREATE the #InvRcpt outside of EXEC (@qry) by trying the following:
<pre><code>IF OBJECT_ID('tempdb..##InvRcpt1') IS NULL
BEGIN
EXEC ('CREATE TABLE #InvRcpt1 (SKU nvarchar(25), ' + @Start + ' int' + @columnsRcpt + ', 
 Vendu int, 
 [Retrait d''inventaire] int, 
 Ajout int, 
 Solde int, ' + @End + ' int)')

END

SELECT *
INTO #InvRcpt
FROM ##InvRcpt1

SELECT *
FROM #InvRcpt
</code></pre>

blocks|key|1761205|text|由于#TmpTable仅在直接查询中使用，所以当您试图通过执行创建的SQL语句创建表时，必须使用全局临时表(##TmpTable)。此外，为了使Sql编辑器允许您使用##TmpTable，您还需要创建一个无用的实例，然后在使用之前删除表。我同意一次只构建一列表是一种更好的方法，但用这种方式制作表格当然是可能的。请记住，还必须使用EXEC语句将数据添加到表中，因为对字段的任何引用都会在编译时导致错误。祝好运!|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1761206|声明|1761207|@DelTable+varchar(100)+=‘DelTable+##TmpTable'，|1761208|@SqlStr+varchar(1000)+=+'‘|1761209|-创建表的无用代码，以便在后面的代码中引用##TmpTable时不会出现错误|1761210|创建表##TmpTable+(+varchar(1))|1761211|exec+(@DelTable)+--运行DelTable+##TmpTable|1761212|--在这里动态地构建创建表字符串，为了演示目的，我将它设置为静态|1761213|set+@SqlStr+=+'create+table+##TmpTable(A+varchar(10)，B+varchar(10)，C+varchar(10))‘|1761214|主管(@SqlStr)|1761215|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|10|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|11|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|12|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|13|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|14|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|U|$]]

Since #TmpTable is only used within the immediate query, you have to use a global temp table (##TmpTable) when you try to create a table by executing a created SQL statement. Also, in order for the Sql editor to allow you to use ##TmpTable, you will also need to create a useless instance of this and then drop the table before use. I agree that building the table one column at a time is a better approach, but doing making a table this way is certainly possible. Remember that you will have to also use EXEC statements to add data to the tables as any reference to the fields will cause errors while compiling. Good luck!
<hr />
declare 
@DelTable varchar(100) = 'drop table ##TmpTable', 
@SqlStr varchar(1000) = '' 
 
--Useless code to create a table so you don't get an error when you reference the ##TmpTable later in the code
 
create table ##TmpTable (a varchar(1)) 
exec (@DelTable) --runs drop table ##TmpTable 
 
-- build your create table string dynamically here, I'm setting it as static for demo purpose 
 
set @SqlStr = 'create table ##TmpTable([A] varchar(10), [B] varchar(10), [C] varchar(10))' 
 
exec (@SqlStr)

I need to create a temp table dynamically since I don't know in advance all the columns I will use.
So let's say I need to create a table with fixed columns
<pre><code>Item int, start int, end int 
</code></pre>
and
variable columns <code>(Rcpt1 int, Rcpt2 int)</code> that I managed to put in an nvarchar variable <code>@columnsRcpt</code> + 2 more variable columns <code>@Start</code> and <code>@End</code>.
So I try to build my query as such :
<pre><code>@qry = 'CREATE TABLE #InvRcpt (SKU nvarchar(25), '
 + @Start + ' int' 
 + @columnsRcpt +', 
 Vendu int, 
 [Retrait d''inventaire] int, 
 Ajout int, 
 Solde int, ' 
 + @End + ' int)'
Exec(@qry)
select * from #InvRcpt
</code></pre>
But it doesn't work, I'm sure that I miss something syntax wise.

Create an SQL temp table using EXEC

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要动态地创建一个临时表，因为我不知道我将要使用的所有列。所以假设我需要创建一个固定列的表Item int, start int, end int 和变量列(Rcpt1 int, Rcpt2 int)，我设法将它放入一个nvarchar变量@columnsRcpt +2多个变量列@Start和@End。因此，我尝试这...

问使用EXEC创建SQL temp表
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用EXEC创建SQL temp表EN