Creating SQL "Loops"-CodePudding

I am working with the R programming language.

I have the following tables stored on a server : "my_table1", "my_table2", "my_table3", "my_table4", "my_table5"

I am trying to write a LOOP that performs SQL statements on these tables. As an example:

library(odbc)
library(DBI)

names = c("my_table1", "my_table2", "my_table3", "my_table4", "my_table5")
limits = abs(as.integer(rnorm(5,100,100)))

mycon = dbConnect(...)

results = list()

for (i in 1:length(names))

{

file_i = dbGetQuery(mycon, "select * from names[i] limit limits[i]")
results[[i]] = file_i

}

final_results <- do.call(rbind.data.frame, results)

But I don't think the dbgetquery() statement is able to recognize the SQL in this format.

Can someone please show me how to fix this?

Thanks!

CodePudding user response：

to create the sql statement you have to use

paste("select * from ",names[i]," limit ",limits[i])

instead of

"select * from names[i] limit limits[i]"

CodePudding user response：

No need for an explicit for loop, we can do it with

results <-
  Map(function(nm, lim) dbGetQuery(mycon, paste("select * from", nm, "limit", lim)),
      dbQuoteIdentifier(mycon, names), limits)

Alternatively, generating the queries up front then lapplying over them:

queries <- paste("select * from", dbQuoteIdentifier(mycon, names), "limit", limits)
results <- lapply(queries, dbGetQuery, conn = mycon)