Greg, 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. Chris > dbSendQuery(con, "create table tmp (id int not null auto_increment primary > key, a char(1), b char(1))") <MySQLResult:(369,1,67)> > 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), row.name=FALSE, append=TRUE) [1] TRUE > dbWriteTable(con, "tmp", cbind(NA, x), row.name=FALSE, 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(is.na(maxId)) 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: http://www.nabble.com/SQL-INSERT-using-RMySQL-tp16640280p16644954.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
