Insert Into using results form a Declare statement

Experts - Need some guidance please...

I am looking for a way to insert vales from a dataset created using a declare statement and i can not figure out exactly how the code needs to read.

Here is a sample of the Declare to get the recordset I need...I works great!
DECLARE @Query varchar(max)
SET @Query = N
'
SELECT
ID,
Name
FROM
Sometable
WHERE
MyCriteria

EXEC  (@Query) AT [LinkedServerName]

How do I use the results from the declare in an INSERT INTO statement? I have a table all set up to paste the records...but do not know how to reference the dataset created in the decare in the From statment of the INSERT INTO code.

See sample below...what do I put in the FROM statement to call the recordsset created from the DECLARE?

INSERT INTO Table1
  (
  ID,
  Name
  )
SELECT
  ID,
  Name
FROM
EXEC  (@Query) AT [LinkedServerName]
Cynthia HillLead ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel Van Der WerkenIndependent ConsultantCommented:
There are two ways to do this. But first, why are you using dynamic SQL? Does the query change based on conditions? If not, then I'd not use dynamic SQL (the declare statement).

What you are looking at could very well be defined as a table value function.

You could then do:

INSERT INTO Table1
SELECT * FROM dbo.MyTableValueFunction(parameter1, parameter2, etc.)

Open in new window


The other way is to create a temp table in your dynamic query and then reference that in your select and insert.
DECLARE @Query varchar(max)
SET @Query = N
'
SELECT 
ID, 
Name
INTO #MyTempTable
FROM
Sometable
WHERE
MyCriteria

Open in new window

Then,

INSERT INTO Table1
  (
  ID,
  Name
  )
SELECT 
  ID,
  Name
FROM #MyTempTable

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you need the DECLARE? You can do that directly in the FROM clause:
INSERT INTO Table1
   (
   ID,
   Name
   )
 SELECT
   ID,
   Name
 FROM (SELECT
       ID,
       Name
       FROM Sometable
       WHERE MyCriteria) AS Q
0
Cynthia HillLead ConsultantAuthor Commented:
Hey Daniel / Vitor - I am using the DECLARE because I'm linking SQL Server to an Oracle DB to retrieve data I need to pull into my teams data warehouse. It has been found that when i try to do a straight query from my Oracle linked server...I receive an error about inconsistent meta data for a particular field in the Oracle table. As a way to get around that, I have been able to use the OPENQUERY function to query from the oracle linked server. However, the current query i am working with is over 8K characters...and OpenQuery does not seem to like that. SO...i am using the DECLARE to try and make things work.  

Daniel - i will look into the table defined function. Thanks..

Regarding the option to make a temp table right in the declare...I assume if I tried to do that...it would try to make the temp table in the ORACLE db, because my from is pointing to the Oracle Linked Server...but i may be wrong on that...and there may be a way that I can specify it make the temp table in the local SQL Server db instead. I will play with this also. Thanks for giving me two options to try! I will loop back with you on these options soon.

Vitor - Hope the explanation above is helpful for you as well. The original problem is that I do not know what to list in the FROM statement so that it will pull from the data set made during the declare.

Thanks,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
I think this should work just fine:

DECLARE @Query varchar(max)
SET @Query = N
'
SELECT
ID,
Name
FROM
Sometable
WHERE
MyCriteria
'

INSERT INTO Table1
  (
  ID,
  Name
  )
EXEC (@Query) AT [LinkedServerName]
0
Cynthia HillLead ConsultantAuthor Commented:
Hey Scott - That method did not work. The error I receive complains of an "unimplemented feature " 

Any thoughts on what should be turned on?

I will continue to search on-line to see if I can find anything.
0
Scott PletcherSenior DBACommented:
Interesting.  Some aspect of remote queries / distributed trans must be turned off.

Check these configuration settings in SQL:
remote access
remote proc trans

And the remote properties/capabilities for the linked server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
CyHill, there's something else that we can do for you regarding this question?
0
Cynthia HillLead ConsultantAuthor Commented:
Thanks all for your assistance on this question. We ended up having to go a different way due to red tape put forth by our IT department.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.