Insert rows in the sql table using dbAppendTable

Question

I am trying to insert data from r to SQL server table. I have to read hundreds of files which are in csv format, I am reading them in r one at a time, process them and then write them to sql (one single table that's why I have to a insert rows)

I am using DBI and odbc package. I am using dbCreateTable to create empty table and dbAppendTable to insert the data.

The number of columns in the empty table is same as with the data which I am trying to insert

library(DBI)
library(odbc)

con <- dbConnect(odbc(), Driver = "SQL Server", Server = "myserver", 
                 Database = "mydb", trustedconnection = TRUE)

t <- read_csv("myfile.csv") %>% select(1,4:7,9:20 )
dbRemoveTable(con, "NEW_SQL_TABLE")
dbCreateTable(conn = con, "NEW_SQL_TABLE", t)
dbAppendTable(conn = con, "NEW_SQL_TABLE", t)

t2 <- dbReadTable(con,"NEW_SQL_TABLE")

I expect t2 to contain same records as t, but t2 has 0

when I ran dbAppendTable, there was a 0 in the console which I am not sure about.

> dbAppendTable(conn = con, "NEW_SQL_TABLE", t)
[1] 0

Answer 1

use dbWriteTable() instead of dbCreateTable() and dbAppendTable().