issue with EXEC sp_executesql

  • Hi Friends,

    How to insert resultset of EXEC sp_executesql into temporary table?

    I tried below option but not luck.

    DECLARE @q varchar(max)

    SET @q = 'insert into #tempTable exec sp_executesql '+@DynamicQuery+''

    EXEC(@q)

    --print @q

    please give an idea

    Thanks Abhas.

  • Hi abhas,

    Let me ask because it is not clear. Where is "#tempTable" created?

    If you are receiving an error, please post the exact error message you are receiving. It may also be helpful to do a "PRINT @q" instead of the "EXEC(@q)" and post the actual code from that.

    abhas (9/17/2014)


    Hi Friends,

    How to insert resultset of EXEC sp_executesql into temporary table?

    I tried below option but not luck.

    DECLARE @q varchar(max)

    SET @q = 'insert into #tempTable exec sp_executesql '+@DynamicQuery+''

    EXEC(@q)

    --print @q

    please give an idea

    Thanks Abhas.

  • After looking at it some more... perhaps the reason you are getting wrong results is that you are trying to do dynamic SQL within dynamic SQL.

    Try this instead:

    DECLARE @q Nvarchar(max)

    SET @q = @DynamicQuery

    --print @q

    insert into #tempTable

    exec sp_executesql @q

    abhas (9/17/2014)


    Hi Friends,

    How to insert resultset of EXEC sp_executesql into temporary table?

    I tried below option but not luck.

    DECLARE @q varchar(max)

    SET @q = 'insert into #tempTable exec sp_executesql '+@DynamicQuery+''

    EXEC(@q)

    --print @q

    please give an idea

    Thanks Abhas.

  • Hi venoy, Thanks for reply.

    I am getting the result from EXEC sp_executesql @DynamicQuery correct. I just want to add the outputof this into temp table.

    I am not able to past code due to security. Still i will try creating dummy. I will update soon

    Thanks,

    Abhas.

  • INSERT INTO #DestinationTable

    EXEC sp_executesql ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    For both solution getting an error, temp table does not exists

  • You have created the temp table before running that insert statement?

    Insert doesn't create the destination table....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply