Solved

Insert into temp table - sp_executesql

Posted on 2012-03-20
3
817 Views
Last Modified: 2012-04-25
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

0
Comment
Question by:tbaseflug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 37744154
try

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

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(dataprod, @sqlX)
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37744654
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
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 37745026
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question