SQL INTO Dynamic Result Temporary Table

Question

I need to save a dynamic sql result to a temporary table #Temp.

The result of a dynamic SQL query is obtained from the result pivot, so the number of columns changes (not fixed).

SET @Sql = N'SELECT ' + @Cols + ' FROM 
        (
           SELECT ResourceKey, ResourceValue 
           FROM LocaleStringResources where StateId ='
+ LTRIM(RTRIM(@StateID)) + ' AND FormId =' + LTRIM(RTRIM(@FormID))
+ ' AND CultureCode =''' + LTRIM(RTRIM(@CultureCode)) + '''
         ) x
        pivot 
        (
            max(ResourceValue)
            for ResourceKey IN (' + @Cols + ')
        ) p ;'

     --@Cols => Column Names which varies in number

Now I need to insert the dynamic sql result into the table #Tempand use this table #Tempwith another existing table to perform joins or something else.

( #Tempa table must exist to perform operations with other existing tables)

How to insert the result of a dynamic SQL query into a temporary table?

thank

Answer 1

select t1.name,t1.lastname from(select * from table)t1.

"select * from table" - dyanmic query. , temp table t1, .

Answer 2

.

SET @Sql = N'SELECT ' + @Cols + ' 
    into ##TempTable
    FROM 
    (
       SELECT ResourceKey, ResourceValue 
       FROM LocaleStringResources where StateId ='
       + LTRIM(RTRIM(@StateID)) + ' AND FormId =' + LTRIM(RTRIM(@FormID))
       + ' AND CultureCode =''' + LTRIM(RTRIM(@CultureCode)) + '''
     ) x
    pivot 
    (
        max(ResourceValue)
        for ResourceKey IN (' + @Cols + ')
    ) p ;'

##TempTable .

##TempTable , , ,

Answer 3

(https://social.msdn.microsoft.com/Forums/sqlserver/en-US/144f0812-b3a2-4197-91bc-f1515e7de4b9/not-able-to-create-hash-table-inside-stored-proc-through-execute-spexecutesql-strquery?forum=sqldatabaseengine),

#Temp:

CREATE TABLE #Temp(columns definition);

, , . , , - .

, PIVOT. , . , SQL Server 1024 ( ) 8060 (http://msdn.microsoft.com/en-us/library/ms143432.aspx). , #Temp ( NULLable).

, CREATE TABLE ( int ):

CREATE TABLE #Temp(c1 int NULL, c2 int NULL, c3 int NULL, ..., c1024 int NULL);

, #Temp , @Cols. .

@Cols. - - , , @Cols , , . , #Temp. - :

@TempCols = N'c1, c2, c3, c4, c5';

@TempCols , @Cols. SQL ( INSERT INTO #Temp (@TempCols) ):

SET @Sql = N'INSERT INTO #Temp (' + @TempCols + N') SELECT ' + @Cols + N' FROM 
        (
           SELECT ResourceKey, ResourceValue 
           FROM LocaleStringResources where StateId ='
+ LTRIM(RTRIM(@StateID)) + ' AND FormId =' + LTRIM(RTRIM(@FormID))
+ ' AND CultureCode =''' + LTRIM(RTRIM(@CultureCode)) + '''
         ) x
        pivot 
        (
            max(ResourceValue)
            for ResourceKey IN (' + @Cols + ')
        ) p ;'

SQL:

EXEC (@Sql) OR sp_executesql @Sql

, #Temp temp c1, c2, c3, ...

MSDN :

, , , .

DROP #Temp, :

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp'

T-SQL (CREATE TABLE, EXEC,... ..., DROP TABLE), , .

Answer 4

IF OBJECT_ID('tempdb..##TmepTable') IS NOT NULL DROP TABLE ##TmepTable
     CREATE TABLE ##TmepTable (TmpCol CHAR(1))
DECLARE @SQL NVARCHAR(max) =' IF OBJECT_ID(''tempdb..##TmepTable'') IS NOT 
NULL DROP TABLE ##TmepTable 
SELECT * INTO ##TmepTable  from [MyTableName]'
    EXEC sp_executesql @SQL
SELECT  Alias.* FROM ##TmepTable as Alias

IF OBJECT_ID('tempdb..##TmepTable') IS NOT NULL DROP TABLE ##TmepTable 

Answer 5

.

• , , .

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

IF OBJECT_ID('tempdb..##abc') IS NOT NULL
DROP TABLE ##abc

2. temp ( ).

SELECT * 
INTO   #temp 
FROM   (SELECT ResourceKey, ResourceValue 
FROM LocaleStringResources 
where StateId ='+ LTRIM(RTRIM(@StateID)) + ' AND FormId =' + LTRIM(RTRIM(@FormID))
+ ' AND CultureCode =' + LTRIM(RTRIM(@CultureCode)) + ') AS S

3. , pivot temp.

DECLARE @str NVARCHAR(1000) 
DECLARE @sql NVARCHAR(1000)      
SELECT @str = COALESCE(@str+',', '') + ResourceKey FROM   #temp 

SET @sql = N'select * into ##abc from (select ' + @str + ' from (SELECT ResourceKey, ResourceValue FROM #temp) as A 
Pivot 
(   
  max(ResourceValue)
  for ResourceKey in (' + @str + ')  
)as pvt) as B'

4. Run the following query to get the collapse result in the following .temp table ##abc

EXECUTE sp_executesql @sql

5. Now you can use ##abcas a table wherever you would like, for example

select * from ##abc

Hope this helps you.