Insert data into temporary table from dynamic quer

问题:

I have the below dynamic query run in SQL Server, connecting to an OLAP server using a linked server, which returns a table as a result.

SET @nSQL = EXECUTE ('SELECT non empty {
[Coded Season].[Coded Season].[Coded Season] *
[Season].[Season].[Season] *
[Product].[Subclass].[Subclass] *
[Product].[Subclass Id].[Subclass Id]
} ON ROWS,{
[Measures].[Pl No of Range Opts]
} ON COLUMNS
FROM RP_C0') AT AS_T_RP_5900_Admin

I am executing it in SQL Server like this:

exec sp_executesql @nSQL;

It returns a table of values. Now I want to insert the data into a temporary table. I have tried the below code, but its not working.

INSERT INTO ##Subclass_Season_AS 
exec sp_executesql @nSQL;

Also tried,

set @strNewQuery ='SELECT '+@nSQL+' INTO ##temptablename '
exec @strNewQuery

Could you please help on this? Thanks!

回答1:

You may want to try to put the INTO statement in your dynamic query.

SET @nSQL = EXECUTE ('SELECT non empty {
[Coded Season].[Coded Season].[Coded Season] *
[Season].[Season].[Season] *
[Product].[Subclass].[Subclass] *
[Product].[Subclass Id].[Subclass Id]
} ON ROWS,{
[Measures].[Pl No of Range Opts]
} ON COLUMNS
INTO ##temptablename
FROM RP_C0') AT AS_T_RP_5900_Admin