[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 781
  • Last Modified:

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

0
robbid
Asked:
robbid
  • 4
  • 2
1 Solution
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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