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]
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]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]
DECLARE @Query varchar(max)
SET @Query = N
'
SELECT
ID,
Name
FROM
Sometable
WHERE
MyCriteria
'
INSERT INTO Table1
(
ID,
Name
)
EXEC (@Query) AT [LinkedServerName]
ASKER
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.
Any thoughts on what should be turned on?
I will continue to search on-line to see if I can find anything.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CyHill, there's something else that we can do for you regarding this question?
ASKER
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.
ASKER
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,