1

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!

TT.
  • 15,774
  • 6
  • 47
  • 88
Emraan
  • 143
  • 1
  • 12
  • 2
    What's the error you are getting? Could you show the `##Subclass_Season_AS` table definition? – gotqn Nov 10 '16 at 07:40
  • Hi, This is the table defintion.CREATE TABLE ##Subclass_Season_AS ([Coded Season] VARCHAR(10), [Subclass Id] VARCHAR(40), [Season] VARCHAR(40), [Pl No of Range Opts] INT). Incorrect syntax is the error I am getting. – Emraan Nov 10 '16 at 08:38
  • Instead using `AT AS_T_RP_5900_Admin` could add the linked server name in front of each table name? – gotqn Nov 10 '16 at 09:08
  • 1
    *"its not working*" >> This is not enough of a problem description. Describe please (in your question) what you mean by that. Does your statement result in a compiler error, a run-time error, incorrect results...? Also, please add a tag to your question for the specific SQL Server version(s) you are using. – TT. Nov 10 '16 at 09:13

1 Answers1

0

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
Christian Delos Reyes
  • 28
  • 3