0

I am having trouble requesting some data that need me to join two tables from different databases using library(RODBC).

Usually, when using RODBC, I go like:

library(RODBC)
dbhandle <- odbcDriverConnect('details to access my database db1')
MyData <- sqlQuery(dbhandle,'SELECT this , that FROM db1_tab5 ')

But what if I want to pass a query like

MyData <- sqlQuery(dbhandle,'SELECT a.this , a.that , b.those
                             FROM db1_tab5 a
                             JOIN db2_tab3 b on a.var = b.var' )

It doesn't work, because obviously 'dbhandle' connects to db1 only. Not to both db1 and db2.

I can more or less work out my problem by importing separately from db1 and db2 to R, and then merge from within R. But to be fair, those tables are quite big (maybe that's why they sit on different db) and I would like to know if I could perform this with RODBC.

I tried to put the two db in the dbhandle definition:

db_both_handle <- odbcDriverConnect('details to db1','details to db2')

but that didn't work (that would have been very surprising, I know).

Any ideas ?

Sylvain
  • 35
  • 4
  • Possible duplicate of http://stackoverflow.com/questions/20355476/rodbc-merge-tables-from-different-databases-channel – r2evans Jun 15 '16 at 15:52
  • Perhaps you can make two different db connections, pull from each, and merge after the pull? This can be done with `merge` or `dplyr::left_join` (and similar functions). – r2evans Jun 15 '16 at 15:53
  • thank you r2evans. I missed the duplicate (I did not use the good keywords I guess). Anyway, I understand now there is no way to perform this with RODBC yet. Thanks. – Sylvain Jun 16 '16 at 15:20

0 Answers0