Using dbplyr for multiple database merging-CodePudding

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.

CodePudding user response：

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')))