Joining Tables from 2 Different Servers

[omario]

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!

[andresrcs]

@omario

Or will establishing "connection_2" automatically cancel "connection_1"?

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

@omario

I would like to join these two tables together and save the resulting table on "connection_1":

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