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

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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')
HainKurtSr. System AnalystCommented:

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

INTO    #tmpTable
FROM    OPENQUERY(dataprod, @sqlX)
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?
All Courses

From novice to tech pro — start learning today.