[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

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

1 Solution
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?
Ken SelviaRetiredCommented:
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')

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now