How to join tables from different SQL databases using R and dplyr?

Question

I use the dplyr (0.7.0), dbplyr (1.0.0), DBI 0.6-1, and odbc (1.0.1.9000). I would like to do something like the following:

db1 <- DBI::dbConnect(
  odbc::odbc(),
  Driver = "SQL Server",
  Server = "MyServer",
  Database = "DB1"
)
db2 <- DBI::dbConnect(
  odbc::odbc(),
  Driver = "SQL Server",
  Server = "MyServer",
  Database = "DB2"
)
x <- tbl(db1, "Table1") %>%
  dplyr::left_join(tbl(db2, "Table2"), by = "JoinColumn") 

but I keep getting an error that really has nothing to do with it. When I use it show_query, it looks like the code is trying to create an SQL query that joins two tables without regard to individual databases. In the documentation for dplyr::left_joinI also tried:

x <- tbl(db1, "Table1") %>%
      dplyr::left_join(tbl(db2, "Table2"), by = "JoinColumn", copy = TRUE) 

But no changes to the exit message or error. Is there any other way to combine tables from separate databases on one server?

Answer 1

merge(), . x <- merge(df1, df2, by = "JoinColumn", all.x = TRUE).

Answer 2

, dplyr:: left_join.

, , . SQL Server , sql().

con <- dbConnect(odbc::odbc(), dsn="DWH" ,  uid="", pwd= "" )

data_db <- tbl( con, sql("SELECT * 
                    FROM DB1..Table1 AS a
                    LEFT JOIN DB2..Table2 AS b ON a.JoinColumn = b.JoinColumn") ) 

data_db% > % ...

, .

Answer 3

, , , () tbl dplyr collect() (b), tbl().

, dplyr , INNER JOIN , . (, , .)

, , in_schema() ( ):

conn <- DBI::dbConnect(
  odbc::odbc(),
  Driver = "SQL Server",
  Server = "MyServer"
)

x <- tbl(src_dbi(conn),
         in_schema("DB1.dbo", "Table1")) %>%
  dplyr::left_join(tbl(src_dbi(conn),
                       in_schema("DB1.dbo", "Table2")),
                   by = "JoinColumn")