Insert into temp table - sp_executesql

I know that the below will not work - I will have no way of knowing how many columns may be in Table1 at any given time - so in theory - was trying to do something like the below???
		DECLARE @sqlX nvarchar(max)
		SET @sqlX = 'SELECT * FROM dbmasterdata.dbo.table1
		
				SELECT  * 
				INTO    #tmpTable 
				FROM    OPENQUERY(dataprod, 'EXEC sp_executesql  @sqlX')

Open in new window

tbaseflugAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ken SelviaConnect With a Mentor RetiredCommented:
If you must use EXEC to get data from a remote server, try;

SELECT * into #helplogins FROM
OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=<yourservername>;',
'SET FMTONLY OFF;EXEC master.dbo.sp_helplogins')
0
 
HainKurtSr. System AnalystCommented:
try

DECLARE @sqlX nvarchar(max)
SET @sqlX = 'SELECT * FROM dbmasterdata.dbo.table1'

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(dataprod, @sqlX)
0
 
lcohanDatabase AnalystCommented:
Why does it need to be so complicated and not just:

SELECT  * INTO  #tmpTable FROM dbmasterdata.dbo.table1
SELECT  * FROM #tmpTable


Are you running against some linked server or something that you need OPENQUERY?
0
All Courses

From novice to tech pro — start learning today.