Search code examples
rsql-serverodbcdbplyr

Using dbplyr for multiple database merging

My database engine in SQL server consists of 2 databases. I'd like to have some joint tables from two different databases but so far I couldn't.

Here is how I tried;

library(odbc)
library(dbplyr)

con <- dbConnect(odbc(),driver='SQL Server',server = 'myserver',database='db1',encoding='windows-1254')

table1 <- tbl(con,in_schema('db1.dbo','abc'))

table2 <- tbl(con,in_schema('db2.dbo','cde'))

as such, it raises an error ;

[SQL Server]Invalid object name 'db1.dbo.abc'.  [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

but this looking table links works fine;

table1 <- tbl(con,in_schema('dbo','abc'))

it works because I already specified database in the connection object but now I want to use a table with same connection but from another database. I cannot declare a new connection, when I do this, it copies tables which is to be joint to my local and it takes too long.

I shouldn't write an native SQL query instead of dbplyr and use one connection object.

Thanks in advance.

Solution

  • You should be able wrap the schema value and table value in dplyr::sql() when trying to use con for db1 to reach db2

    table1 <- tbl(con,in_schema('dbo','abc'))
table2 <- tbl(con,in_schema(dplyr::sql('db2.dbo'),dplyr::sql('cde')))