R Import Multiple Tables from database using sqlQuery

Question

I know how to import a table from the database, it works; but what if I have 6 tables, is there a better way to import from 6 different tables and create 6 different data.frames ?

Notes <- sqlQuery(dbConnection, " SELECT * FROM Table1")

??

is there a better way to import? Better way from what? Calling SQL commands from a database? — Parfait
– Parfait, Commented Dec 20, 2016 at 1:18
do you not know how to read ? in there a better way to import more than one table with the sqlQuery ? without repeating that statement 6 times ? — Ray Kodiak
– Ray Kodiak, Commented Dec 20, 2016 at 16:51

Parfait · Accepted Answer · 2016-12-20 18:01:07Z

4

Consider an lapply call across a list of tables:

tableList <- c("Table1","Table2","Table3","Table4","Table5","Table6")

# BUILD LIST OF DFS FROM QUERIES
dfList <- lapply(tableList, function(t) sqlQuery(dbConnection, paste0("SELECT * FROM ",  t))

# NAME EACH ELEMENT TO CORRESPONDIND TABLE NAME
dfList <- setNames(dfList, tableList)

And then if you want separate dataframes use list2env but you can always work directly from dfList to avoid many objects in global environment:

list2env(dfList, envir=.GlobalEnv)

answered Dec 20, 2016 at 18:01

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ray Kodiak Over a year ago

Great, thank you, this is exactly what I was looking for, thanks a lot!!!! Parfait

Ray Kodiak Over a year ago

dfList <- lapply(tableListBase, function(t) fetch(dbSendQuery(con, paste0("SELECT * FROM ", t))))

Collectives™ on Stack Overflow

R Import Multiple Tables from database using sqlQuery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related