4

I am trying to insert a data.frame in the MySQL database using RODBC. The command I am using 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 the data has a primary key which is auto-increment. My table has total of 7 columns including the primary key. In my data frame, I have 6 columns because I don't want to insert the PK myself. 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 it is trying to insert '1' as the primary key when there is already a record with 1 as its PK. Any idea how I can avoid this using sqlSave()?

Thanks in advance.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Sami
  • 153
  • 1
  • 1
  • 5

2 Answers2

1

You can try adding seventh column in your dataframe, and assing value of NULL or 0 for PK column data. Then MySQL will generate value for it automatically.

Petr Abdulin
  • 33,883
  • 9
  • 62
  • 96
  • First of all, thanks for the quick response. I am surprised to see that the answer was something that simple. Anyways, I added another column with 0 values and now I am getting the following error: Error in dimnames(x) <- dn : length of 'dimnames' [2] not equal to array extent – Sami Jun 15 '11 at 06:57
0

Use sqlUpdate so that this will update rows based on the matching primary key. If you really want to append you'll need to create unique ids for the new rows before writing to the database. This is pretty simple in R but you will need to ensure they are unique against what is already in the db as well as in your new data in R.

mdsumner
  • 29,099
  • 6
  • 83
  • 91
  • I don't think the asker wanted to update existing rows in the table. Rather he wanted to append new rows. Furthermore, I've run into problems trying to insert my own ids for a table whose PK expects to be auto-generated. Surely there's a better way? – Ben Apr 23 '15 at 21:05
  • Super impressed by this comment 4 years later! I have no idea what this is any more. – mdsumner Apr 24 '15 at 15:06
  • @Ben : You are right, I think R doesnt provide this facility to insert using sqlSave for autoincrementing Identity column. – indra_patil Jun 19 '15 at 09:46