sp_executesql to update a temp table

Question

I commonly use, and can easily find documentation on the internet for inserting into a temp table from an sp_executesql command:

 insert into #temp (
    column1,
    column2,
    column3
 )
 exec sp_executesql @myQueryString

However, what I am having trouble finding any documentation on, is how to update the temp table from sp_executesql, such as setting the already existing column2 to a new value from the results of the stored procedure.

Is this possible?

score 2 · Accepted Answer · answered Jul 12 '14 at 18:04

2

It isn't possible to use the sp_executesql resultset directly in an UPDATE statement.

You would need to insert the result into a different table variable / temp table / permanent table that you then use to do your update against #temp.

You can access the temp table directly from the code in @myQueryString though.

So for example if @myQueryString was UPDATE #temp SET column1 = 2 this would also work.

answered Jul 12 '14 at 18:04

Martin Smith

438,706
87
741
845

Okay, tracking. That's unfortunate, but i'll do that. Thanks. – dthree Jul 12 '14 at 18:15

score 1 · Answer 2 · answered Jul 13 '14 at 03:58

Possible but probably not worth it. You can use openrowset to do this. You can look at my example below: You will need to replace the server name with the server in question.

use master 

go

drop table #tempT
create table #tempT
(
    name sysname

) 

insert into #tempT
values ('backupmediafamily')

select * from #tempT
update t 
set name = 'new'
from ( SELECT * FROM OPENROWSET('sqloledb', 'server=YourServerName;database=master;trusted_connection=yes','exec msdb.dbo.sp_executesql N''select * from sys.tables''')) a
inner join #tempt t on t.name = a.name

select * from #tempT

If you wanted something a little more dynamic you could try this:

drop table #tempT
create table #tempT
(
    name sysname

) 

insert into #tempT
values ('backupmediafamily')

select * from #tempT
declare @sp_executesqltext nvarchar(max) = 'select * from sys.tables where name like ''%backup%'' '
select @sp_executesqltext = replace(@sp_executesqltext,'''','''''''''')
declare @sql nvarchar(max)
set @sql = 'update t 
            set name = ''new''
            from ( ' +
    'SELECT * FROM OPENROWSET(''sqloledb'', ''server='+@@SERVERNAME+';database=master;trusted_connection=yes'','  +
    '''exec msdb.dbo.sp_executesql N'''''+@sp_executesqltext+''''''')) a
    inner join #tempt t on t.name = a.name' 

exec sp_executesql @sql

select * from #tempT

Lots of quotes so it will be a treat to debug if things break.

sp_executesql to update a temp table

2 Answers2