September 17, 2014 at 4:28 am
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
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
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
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
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
September 17, 2014 at 6:08 am
Hi,
For both solution getting an error, temp table does not exists
September 17, 2014 at 6:14 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply