How do I write SQL to pull data from a linked server and insert into a local table with criteria from local table

I have set up a linked server to import data from one database (Oracle) into my SQL Server 2005 Express.  I am using openquery linking to my linked Oracle server.  I want to be able to use information from a table in the SQL server in the WHERE statement from the Oracle server.  Please see code below because I know this doesn't make sense as I have written it.   Thanks, in advance.

This is what I want to do: 
INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
select * from openquery(GP_0076, 'SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig FROM CALL_TODAY
 WHERE APPLIC_NUM in (select Applic_Num from tbl_AppSelect where Count_ASA = 1) GROUP BY APPLIC_NUM')
---------------------------The code below works because I am explicitly give choices for APPLIC_NUM (100,101).  In the code above, the table 'tbl_AppSelect' is on the SQL server, not the Linked Oracle server.
 
INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
select * from openquery(GP_0076, 'SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig FROM CALL_TODAY
 WHERE APPLIC_NUM in (100, 101) GROUP BY APPLIC_NUM')

Open in new window

robbidAsked:
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.

k_rasuriCommented:
while calling from a different database you should use the servername and databasename to call a table. so your query should look something like this

INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
select * from openquery(GP_0076, 'SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig FROM CALL_TODAY
 WHERE APPLIC_NUM in (select Applic_Num from servername.databasename.schema.tbl_AppSelect where Count_ASA = 1) GROUP BY APPLIC_NUM')

if you are a admin on any of the systems, configure linked systems and use the above convention (servername.dbname.schema.table) to call the tables
0
robbidAuthor Commented:
Thanks for the quick response.  I have modified the SQL as described, but now I get the error below.  I don't see where a parenthesis is missing.  Do you see what could be wrong?

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00907: missing right parenthesis
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].

INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
select * from openquery(GP_0076, 'SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig FROM CALL_TODAY
 WHERE APPLIC_NUM in (SELECT Applic_Num from wvhurrias002.CSCStats.dbo.tbl_AppSelect where Count_ASA = 1) GROUP BY APPLIC_NUM')

Open in new window

0
k_rasuriCommented:
you might have forgot to include the last parenthesis  ')' while executing the statement. just make sure. i think you are executing the query in sql server. if you have already setup the linked server then you can use the query like below, instead of a OPEN QUERY

INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
select * from SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig
FROM servername.dbname.schemaowner.CALL_TODAY
WHERE APPLIC_NUM in (SELECT Applic_Num from tbl_AppSelect where Count_ASA = 1)
GROUP BY APPLIC_NUM
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
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

robbidAuthor Commented:
You're right.  I'm using SQL server.  I'm not sure what I'm doing wrong, but I double checked the code above and the code snippet is correct and there was a closing right parenthesis.  I tried re-writing based on your latest example, but now I'm getting a different error, shown below:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDAORA'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].

INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig 
FROM GP_0076.DTA.ACC.CALL_TODAY 
WHERE APPLIC_NUM in (SELECT Applic_Num from tbl_AppSelect where Count_ASA = 1) 
GROUP BY APPLIC_NUM

Open in new window

0
robbidAuthor Commented:
Your answer led to the example below which worked....<DBNAME>..<SCHEMA><TABLE>.  
Thanks again for your help!



INSERT INTO tbl_ASATempWorking(Applic_Num, Num_ASASecs, Num_Calls)
SELECT APPLIC_NUM, Sum(RING_TIME+QUEUE_TIME+DELAY_TIME) as ASASeconds, Count(ORIG) as CountOfOrig 
FROM GP_0076..ACC.CALL_TODAY 
WHERE APPLIC_NUM in (SELECT Applic_Num from tbl_AppSelect where Count_ASA = 1) 
GROUP BY APPLIC_NUM

Open in new window

0
robbidAuthor Commented:
Teriffic!  You just made my weekend. Nice to end the week with something working!
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 2005

From novice to tech pro — start learning today.