Joining Tables from 2 Different Servers

I have set up two different connections in R:

connection_1 <- dbConnect(odbc::odbc(), driver = my_driver, database = "database_1", uid = "my_id", pwd = "my_pwd", server = "server_1", port = "my_port)

connection_2 <- dbConnect(odbc::odbc(), driver = my_driver, database = "database_2", uid = "my_id", pwd = "my_pwd", server = "server_2", port = "my_port)

I have a table stored in "connection_1" (table_1), and another table stored in "connection_2" (table_2) . I would like to join these two tables together and save the resulting table on "connection_1":

dbGetQuery(connection_1, "create table my_table as select * from connection_1.table_1 a inner join connection_2.table_2 B on A.Key_1 = B.Key_2")

But I am not sure if this is possible in R.

  • Does anyone know if the code I have written can be changed to do this?
  • Or will establishing "connection_2" automatically cancel "connection_1"?
  • I am using Netezza SQL.

Thank you!

Both connections are independent of each other, you can work with both at the same time

What is your problem with this part? Once you have retrieved the data from the queries, they are like any other data frame so you can perform a join using dplyr join operations like left_join(), inner_join(), etc. After that, you can write the result back into the first database with odbc::dbWriteTable()

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.