从动态sql字符串变量Select INTO Temp table

Question

我有一个构建动态SQL查询的过程，简化为

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

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

SELECT * INTO #myTempTable FROM ( @mySQLQuery) x

基本上就是我想做的。

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

感谢您的任何建议`

Answer 1

我能想到的唯一方法就是在tempdb中持久化一个对象。临时表仅在创建临时表的会话中持久存储，这意味着如果使用动态SQL语言创建临时表，则不只在sp_executesql中为该会话持久存储临时表。

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

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

因此，您需要在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;

Answer 2

我使用了两种方法。

如果您事先知道临时表的结构，您可以这样做：

CREATE TABLE #temp (<column list>);

SET @my_sql = <your query syntax, without the INTO clause>;

INSERT #temp (<column list>)
EXECUTE sp_executesql @my_sql;

另一方面，如果您的表结构事先未知，则可以使用全局临时表(##temp)而不是本地临时表(#temp)：

EXECUTE sp_executesql <your query with INTO ##temp etc>

可以从EXECUTE语句外部的过程访问全局表(##temp)。

Answer 3

临时表的作用域只有在会话处于活动状态时才会受到限制。

在您的示例中，当您使用sp_executesql时，一旦完成对sp_executesql的调用，本地临时表就会超出作用域。

您需要创建全局临时表。

下面是一个小例子：

create table dbo.test_mytable( col1 int );
GO

insert into dbo.test_mytable
select 1 union
select 2;
go

create or alter procedure dbo.test_myproc( @mytable varchar(255), @mycol varchar(255) )
as
begin
  declare @mysql varchar(4000);

  drop table if exists ##mytemptable;
  set @mysql = 'select ' + @mycol + ' from ' + @mytable;
  set @mysql = 'select * into ##mytemptable from (' + @mysql + ')  X';

  exec (@mysql);
  select * from ##mytemptable;
end;
GO

exec dbo.test_myproc 'dbo.test_mytable', 'col1';

当您运行上面的代码片段时，您将看到如下结果：

col1
1
2