create table #test (
 Value varchar( 255 )
                    )
                   
create table #test1 (
 Value varchar( 255 )
                     )                    
                   
insert #Test values('Первая строка')
insert #Test values('Вторая строка')

delete #test1

declare @TableName varchar( 255 )
       ,@MySelect  varchar( 255 ) 

select @TableName='#Test'
select @MySelect = 'select Value from'+' '+ @TableName

insert #test1
exec(@MySelect)

select * from #test1 

drop table  #test
drop table #test1