Dynamic OpenQuery into temp table SQL Server

Dear experts,

I would like to create a temp table from a openquery.  like the following
declare @sql varchar(1000)
select @sql = 'select * into #Temp from openquery(Oracle server name, ''select count(*) from SomeTable'')'
exec (@sql)
select * from #Temp

I got a error message saying Invalid object name '#Temp'.

Please help.  Thank you!
SeekAnswerAsked:
Who is Participating?
 
RDWaibelCommented:
Try this:
declare @sql varchar(1000)
select @sql = 'select * into ##Temp from openquery(Oracle server name, ''select count(*) from SomeTable'')'
exec (@sql)
select * from ##Temp


0
 
SeekAnswerAuthor Commented:
Thank you.  It worked!  Since it is a global temp table.  Is there a performance implication?
0
 
RDWaibelCommented:
Only if you don't Delete them when you are done
 
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RDWaibelCommented:
declare @sql varchar(1000)
select @sql = 'select * into ##Temp from openquery(Oracle server name, ''select count(*) from SomeTable'')'
exec (@sql)
select * from ##Temp
Drop Table ##Temp
0
 
AmitGKCommented:
Instead of using fixed table name "##Temp" use a dynamic table name, so you don't have issues when multiple users simultaneously call this code snippet. Sometimes it might result in sending across incorrect data to respective users.

declare @sql varchar(1000), @TempTableName varchar(100)
Select @TempTableName = Replace(convert(varchar(100), NewID()), '-', '')
select @sql = 'select * into ##' + @TempTableName + ' from openquery(Oracle server name, ''select count(*) from SomeTable'')'
exec (@sql)
exec ('select * from ##' + @TempTableName )
exec ('drop table ##' + @TempTableName )
0
 
SeekAnswerAuthor Commented:
Thank you both!  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.