Test speed of dbAppendTable

gistfile1.txt

# Testing methods for inserting data from a file into a SQLite DB

library(DBI)

db<-dbConnect(RSQLite::SQLite(),dbname=":memory")

dbExecute(db,
          "CREATE TABLE TABLE1(
             MARKS INTEGER,
             ROLLNUM INTEGER,
             NAME TEXT
           )"
)

df <- data.frame(MARKS = sample(1:100, 100000, replace = TRUE), 
                 ROLLNUM = sample(1:100, 100000, replace = TRUE), 
                 NAME = stringi::stri_rand_strings(100000, 10))

write.table(df, file.path(tempdir(), "dbtest.txt"), sep = "|")
            

bench::mark({
  tb <- read.delim(file.path(tempdir(), "dbtest.txt"), sep = "|")
  dbAppendTable(db, "TABLE1", tb)
})