Post reply

issue with EXEC sp_executesql

  • September 17, 2014 at 4:28 am

    #311622

    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.

  • September 17, 2014 at 4:40 am

    #1745994

    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.

  • September 17, 2014 at 4:43 am

    #1745995

    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.

  • September 17, 2014 at 4:45 am

    #1745996

    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.

  • September 17, 2014 at 4:52 am

    #1746000

    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

  • September 17, 2014 at 6:08 am

    #1746021

    Hi,

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

  • September 17, 2014 at 6:14 am

    #1746023

    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