0

I am getting the names of tables from a database and I want to select all the data from each table and create a dataframe automatically. The code

nm = dbListTables(connection)

creates a vector like:

[1] "Account"                          "Credit__Amortization_Payment__c" 
[3] "Credit__Amortization_Schedule__c" "Credit__Loan_Payment__c"         
[5] "Credit__Loan__c"

As an example,

 nm = c("Account", "Credit__Amortization_Payment__c", "Credit__Amortization_Schedule__c", "Credit__Loan_Payment__c", "Credit__Loan__c")

I create a list of a query for each value in a loop: 

qry <- list()
for (i in 1:length(nm)) {
  qry[i] <- paste0("select * from ", nm[i])
  }

Then I can't figure out how to save the name of the data frame as the values from the vector, so I have to manually type it out like this, but I would like to just loop through it saving a data frame with exactly the same name and structure from the list nm.

q = paste0(qry[1])
rs <- dbSendQuery(con, q)
Account <- dbFetch(rs, n=-1)

I repeat it here for each value in the vector manually but I would like to do this automatically based on the values in nm = dbListTables(connection)

q = paste0(qry[3])
rs <- dbSendQuery(con, q)
Credit__Amortization_Schedule__c <- dbFetch(rs, n=-1)
Zheyuan Li
  • 71,365
  • 17
  • 180
  • 248
Spruce Island
  • 425
  • 1
  • 4
  • 10

1 Answers1

0

I think assign is what you are looking for, e.g. 

> assign("x", 5)
> x
[1] 5

In your case e.g. for query 1

q <- paste0(qry[1])
rs <- dbSendQuery(con, q)
assign(nm[1], dbFetch(rs, n=-1))

should work. Also have a look at the R FAQ.

rhole
  • 440
  • 2
  • 8
  • That works great. I just could not ask the question correctly to find the way to find assign. Now everything works in one loop: for (i in 1:length(nm)) { qry[i] <- paste0("select * from ", nm[i]) q = paste0(qry[i]) rs <- dbSendQuery(con, q) assign(nm[i], dbFetch(rs, n=-1)) } – Spruce Island May 06 '16 at 14:12