From: Chris Stubben <>
Date: Fri, 11 Apr 2008 19:57:17 -0700 (PDT)


If you have a MySQL table with an auto_increment field, you could just insert a NULL value into that column and the database will increment the key (it may not work in SQL STRICT mode, I'm not sure). I don't think there's any way to specify which columns you want to load data into using dbWriteTable yet, but that would be a nice feature since LOAD data now allows that (and SET syntax and other options).

Try this code below - I used cbind(NA, x) to insert a null into the first column.


> dbSendQuery(con, "create table tmp (id int not null auto_increment primary
> key, a char(1), b char(1))")

> x<-data.frame( a=letters[1:3], b=letters[4:6])
> x
  a b
1 a d
2 b e
3 c f
> dbWriteTable(con, "tmp", cbind(NA, x),, append=TRUE)
[1] TRUE
> dbWriteTable(con, "tmp", cbind(NA, x),, append=TRUE)
[1] TRUE
> dbReadTable(con, "tmp")

  id a b
1 1 a d
2 2 b e
3 3 c f
4 4 a d
5 5 b e
6 6 c f

Gregory. R. Warnes wrote:
> Hi All,
> I've finally gotten around to database access using R. I'm happily
> extracting rows from a MySQL database using RMySQL, but am having
> problems appending rows to an existing table.
> What I *want* to do is to append rows to the table, allowing the
> database to automatically generate primary key values. I've only
> managed to add rows by using
> dbWriteTable( con, "past_purchases", newRecords, overwrite=FALSE,
> append=TRUE, ...)
> And this only appears to properly append rows (as opposed to
> overwriting them) IFF
> 1) the row names for newRecords are new unique primary key values,
> 2) the argument row.names is TRUE.
> If row.names is FALSE, the records will not be appended, even if
> newRecords contains a column (named 'id') of unique values that
> corresponding to the primary key (named 'id').
> It appears that in this case, the row names on the data frame are
> still being used for the primary key, and since overwrite is FALSE,
> the new records are being silently dropped.
> I did manage to get things working by doing the following:
> ## get the last used id value (primary key)
> maxId <- dbGetQuery(con, "SELECT MAX(id) FROM past_purchases")[1,1]
> maxId
> if( maxId <- -1
> ## add the new unique primary keys as row names
> rownames(fulldata) <- maxId + 1:nrow(fulldata)
> ## now write out the data
> dbWriteTable(con, "past_purchases", value=fulldata, overwrite=FALSE,
> append=TRUE, row.names=TRUE)
> Is there a better way to accomplish this task? (Session info is below)
> Thanks!,
> -Greg

View this message in context:
Sent from the R help mailing list archive at

______________________________________________ mailing list
PLEASE do read the posting guide
and provide commented, minimal, self-contained, reproducible code.
Received on Sat 12 Apr 2008 - 03:01:16 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 14 Apr 2008 - 02:30:28 GMT.

Mailing list information is available at Please read the posting guide before posting to the list.

list of date sections of archive