TSQL从动态sql中选择到Temp表。

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.

但是我如何将两者结合起来，这样我就可以将结果直接放到temp表中。

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.

呃. .为了克服这一部分，我已经开始使用execute来生成一个全局临时表。

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 view in the execute command and use that to load the local temp table in the stored procedure.

在execute命令中创建一个视图，并使用该视图在存储过程中加载本地临时表。

Create a global temp table in the execute command and use that to load the local temp table.

在execute命令中创建一个全局临时表，并使用该表加载本地临时表。

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!

谢谢!

5 个解决方案

#1

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

#2

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;

#3

I can think of a few ways to select data from a table whose name was generated dynamically, without using global temporary tables.

我可以考虑一些方法，从一个动态生成名称的表中选择数据，而不使用全局临时表。

Solution 1

解决方案1

Build the string that contains the name of your source table, and make your entire query dynamic.

构建包含源表名称的字符串，并使整个查询动态。

Solution 2

解决方案2

This solution, involving a table variable, will work, provided that you know...

这个解决方案涉及一个表变量，如果您知道…

...the number, name, and type of the columns...
…列的编号、名称和类型……
...the name of the primary key column EDIT: (ok, apparently this is not strictly true, as it can be generated dynamically)...
…主键列的名称:(好吧，显然这并不是完全正确的，因为它可以动态生成)……

in your source table.

在你的源表。

-- Number of rows in your source table
DECLARE @RowCount INT;
SELECT @RowCount = SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = EmployeesWeek_ + CONVERT( VARCHAR(10),WEEK(GETDATE()) ) AND (index_id < 2);

-- All columns from your source table
DECLARE @col1 int;
DECLARE @col2 varchar(50);
DECLARE @col3 varchar(5);
DECLARE @col4 int;
DECLARE @col5 smalldatetime;
DECLARE @col6 bit;

-- This table variable will store the data
DECLARE @Dump TABLE (
    col1 int,
    col2 varchar(50),
    col3 varchar(5),
    col4 int,
    col5 smalldatetime,
    col6 bit
)

DECLARE @RowIter INT = 1;
WHILE @RowIter <= @RowCount
BEGIN
    DECLARE @QueryString NVARCHAR(MAX) = CONCAT(N'
            SELECT
                @col1 = ID,
                @col2 = Name,
                @col3 = NameAbbr,
                @col4 = UpdatedBy,
                @col5 = DateUpdated,
                @col6 = Active

            FROM (
                SELECT
                    ROW_NUMBER() OVER (ORDER BY PKArea ASC) AS RowNum,
                    *
                FROM
                    EmployeesWeek_',WEEK(GETDATE()),'
            ) sq

            WHERE
                RowNum = ', @RowIter , '
            ;
        ');
    EXEC dbo.sp_executesql
        @QueryString,
        -- OUTPUT VARIABLE DECLARATION
        N'
            @col1 int out,
            @col2 varchar(50) out,
            @col3 varchar(5) out,
            @col4 int out,
            @col5 smalldatetime out,
            @col6 bit out
        ',
        -- ALL OUTPUT VARIABLES
        @col1 out,
        @col2 out,
        @col3 out,
        @col4 out,
        @col5 out,
        @col6 out
    ;

    INSERT INTO @Dump values(@col1, @col2, @col3, @col4, @col5, @col6);

    SET @RowIter = @RowIter + 1;

END

SELECT * FROM @Dump;

My intuition says this is potentially slow on large datasets, and I don't know if you can make this query dynamic (it should be possible by querying the list of column names, using dirty hacks to build the string that contains the list of columns, etc., but for now I can't spend any more time on this...), enabling you to select data from a table with any number of columns; but again, this is SQL...

我的直觉说这可能是缓慢在大型数据集上,我不知道如果你可以查询动态(应该可以通过查询列名称列表,使用肮脏的黑客构建字符串包含列的列表,等等,但现在我不能花更多的时间在这…),使您能够选择与任意数量的数据从一个表列;但是，这是SQL…

#4

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)

#5

How I did it with a pivot in dynamic sql (#AccPurch was created prior to this)

我如何在动态sql中使用pivot (#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

#1