Append two tables inside sqlite database using R-CodePudding

I have two very large csv files that contain the same variables. I want to combine them into one table inside a sqlite database - if possible using R.

I successfully managed to put both csv files in separate tables inside one database using inborutils::csv_to_sqlite that one imports small chunks of data at a time.

Is there a way to create a third tables where both tables are simply appended using R (keeping in mind the limited RAM)? And if not - how else can I perform this task? Maybe via the terminal?

CodePudding user response：

We assume that when the question refers to the "same variables" that it means that the two tables have the same column names. Below we create two such test tables, BOD and BOD2, and then in the create statement we combine them creating table both. This does the combining entirely on the SQLite side. Finally we use look at both.

library(RSQLite)
con <- dbConnect(SQLite())  # modify to refer to existing SQLite database

dbWriteTable(con, "BOD", BOD)
dbWriteTable(con, "BOD2", 10 * BOD)

dbExecute(con, "create table both as select * from BOD union select * from BOD2")

dbReadTable(con, "both")

dbDisconnect(con)