How to skip primary key in sqlSave () command?

Question

I am trying to insert data.frame into a MySQL database using RODBC. The command I use is the following:

sqlSave(channel,dbData,tablename='table_name', append=TRUE,safer=TRUE,fast=FALSE,verbose=TRUE) 

Now the table in which I am trying to insert data has a primary key, which is automatically incremented. My table consists of 7 columns, including the primary key. I have 6 columns in my data frame because I do not want to insert the PC itself. However, when I run the command, I get the following error:

 23000 1062 [MySQL][ODBC 5.1 Driver][mysqld-5.5.13]Duplicate entry '1' for key 'PRIMARY' 

From the above error, I understand that she is trying to insert "1" as the primary key when there is already an entry with 1 as her PC. Any idea how I can avoid this using sqlSave ()?

Thanks in advance.

Answer 1

You can try adding the seventh column in your data framework and evaluate to NULL or 0 for PK column data. Then MySQL automatically generates a value for it.

Answer 2

Use sqlUpdate to update rows based on the corresponding primary key. If you really want to add, you need to create unique identifiers for new lines before writing to the database. It's pretty simple in R, but you will need to make sure they are unique against what is already in db, as well as in your new data in R.