从动态sql字符串变量中选择INTO Temp表 [英] Select INTO Temp table from dynamic sql string variable

问题描述

我有一个程序可以构建简化为的动态SQL查询

I have a procedure that builds out a dynamic SQL query simplified as

@mySQLQuery ='SELECT'+ @myCol +'FROM'+ @myTable

我想将此查询选择到临时表中，以便稍后在我的过程中使用，但我找不到正确的语法.

I would like to select this query into a temp table to be used later in my procedure, but I can't figure out the right syntax.

SELECT * INTO #myTempTable FROM(@mySQLQuery)x

基本上就是我想做的.

我尝试了 sp_executeSQL'SELECT * INTO #myTempTable FROM('+ @mySQLQuery +')x'在我的程序中，那也不起作用.

I tried sp_executeSQL 'SELECT * INTO #myTempTable FROM (' + @mySQLQuery + ') x' within my procedure and that didn't work either.

感谢您的任何建议`

推荐答案

我能想到的唯一方法是将对象保留在 tempdb 中.临时表仅在创建它们的会话中持久存在，这意味着，如果您使用动态SQL创建临时表，则不仅在 sp_executesql 中对该会话具有持久性./p>

The only way I can think of you could achieve this would be to persist an object in tempdb. Temporary tables only persist for the session they are created in, meaning that if you create a temporary table using dynamic SQL, isn't only persist for that session in sp_executesql.

EXEC sp_executesql N'SELECT 1 AS one INTO #test;';

--This'll fail
SELECT * FROM #test;

因此，您需要在 tempdb 中使用持久化表:

Therefore you'll need to use a persisted table in tempdb:

DECLARE @NyCol sysname,
        @MyTable sysname,
        @MySchema sysname;
--Assume these are set somewhere

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT ' + QUOTENAME(@MyCol) + @CRLF +
           N'INTO tempdb.dbo.MyTempTable'  + @CRLF +
           N'FROM ' + QUOTENAME(@MySchema) + N'.' + QUOTENAME(@MyTable) + N';';

EXEC sp_executesql @SQL;

--Do Stuff

--Clean up

DROP TABLE tempdb.dbo.MyTempTable;

这篇关于从动态sql字符串变量中选择INTO Temp表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！