While supporting flexible schema, often it is required to construct and execute dynamic queries.
It is also sometimes required to keep data in temp tables for processing.
Creating temp tables in dynamic sql puts the table out of context for the connection.
Once solution is to create a table in code, then create the definition for the table using dynamic sql and then dump data into the table via dynamic sql as shown below.
create table #t (id int)
exec( N'alter table #t add ActivityId int , AssetId int')
declare @sql nvarchar(max)
set @sql = 'select * from ActivityExportData'
insert into #t EXECUTE sp_executesql @sql
alter table #t add id_identity int identity(1,1)
select * from #t
drop table #t
