Updating Mysql table using sqlSave in R

Posted on

Question :

I’m new to R and I’m trying to add new tables in the database, the problem is that it always shows this message:

Error in sqlSave(connection, dado, tablename = "teste", rownames = FALSE,  : 
unable to append to table ‘teste’


dado = pegarDados(arquivoNome,TAG)
colnames(dado) = pegarNomes(arquivoNome,TAG)
sqlSave(connection, dado, tablename = "teste",rownames = FALSE ,colnames = FALSE,append=TRUE)

Does anyone know what causes this and how to solve it? I’m using the RODBC package.


Answer :

Probably this is happening because you had already created the “test” table before, at some point, and now you are trying to write to it again. To find out if this is, run sqlQuery(connection, "drop table teste") before sqlSave() . If you want to update the table, and do not save on top, you can use the sqlUpdate() function, for example.


Once in a job I had to incrementally add tables to a database. The solution I used was this one:

    ## Cria uma conexão com o banco de dados
con <- dbConnect(drv, user="usuario_do_banco", password="senha", dbname="nome_do_banco", host="url_do_banco")

## Adiciona linha a linha se tabela regras existir ou cria uma nova caso contrário
if(dbExistsTable(con, "regras")) {
  dbGetQuery(con, "delete from regras")
  for (i in 1:length(regras[,1])) {
    insere <- paste('insert into regras values (',"'",regras[i,1],"'",",","'",regras[i,2],"'",',',"'",regras[i,3],"'",',',"'",regras[i,4],"'",',',"'",regras[i,5],"'",')', sep="") ## Formatação específica do meu insert
    dbSendQuery(con, insere)
} else {
    dbWriteTable(con, "regras", regras, row.names=F)

In this case I was using Postgres through the RPostgeSQL package, but the RMySQL package has the same commands and should work the same way.

In case you just want to create a table in the database from a data.frame simply use:

dbWriteTable(con, "nome_da_tabela_a_ser_criada", data_frame, row.names=F)

I prefer RMySQL instead of RODBC.


Leave a Reply

Your email address will not be published. Required fields are marked *