Select data from one data source and store it in another

Question

I am using RODBCext to create a parameter query to retrieve data from a DB2 mainframe. I want to insert that data into a table in a local MYSQL (on the same network) to be able to access the data much more quickly and on the fly with a Shiny app. i am not sure how to do that. The below sql works to retrieve my db2 data, but i cant find help learning how to create the rmysql connection in the same r.script to insert it automatically into the mysql db. any help, tips or pointers would be appreciated.

library(RODBCext)  
library(RMySQL)  
##  
DB2Conn1 <- odbcConnect("dsn", uid = "xxx", pwd = "xxx")  
##################################################################  

plan_id <- paste("Select Field1, Field2 FROM  DB2.Table1 
where Field2 = 'M' ")

Have a look at the `RMySQL` pdf to see how to make the connection. Not very different from RODBC — CuriousBeing, Feb 18 '16 at 07:15
Thanks for the response. I did review that documentation, but i need to somehow simultaneously load the from the db2 environment into mysql because the data is too large to save and then requery into mysql. Hope that makes sense? — cowboy, Feb 19 '16 at 18:57

score 0 · Accepted Answer · answered Feb 24 '16 at 18:26

0

This worked. I used the Insert Into to update the table. I first had to create the table with the column headers. Worked fine. Thanks for your help.

answered Feb 24 '16 at 18:26

cowboy

613
5
20

Select data from one data source and store it in another

1 Answers1