RODBC Error - Cannot insert explicit value for identity column

Question

I'm trying to append records to an existing SQL table using the sqlSave method in the RODBC package.

df <- data.frame(EmployeeID = c(NA, NA, NA), EmployeeName=c("Bob", "Sue", "Jane"))

sqlSave(myconn, dat=df, tablename = "Employees", append = TRUE, rownames = FALSE, colnames = FALSE, verbose = TRUE, safer = TRUE, 
        addPK = FALSE, typeInfo, varTypes, fast = TRUE, test = FALSE, nastring = NULL)

However, I keep getting the error

[RODBC] Failed exec in Update 23000 544 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF.

My table should be creating IDs automatically. What gives?

Have you tried leaving off the EmployeeID column from the data.frame? With this method, it looks like you're trying to append NULL values into the EmployeeID column. Better to leave those values unspecified for the append. — MrFlick
@MrFlick yes, in which case I get the error "length of 'dimnames' [2] not equal to array extent". This Post is very similar, but the answer doesn't really help. — Ben

Ben Ben · Accepted Answer · 2015-04-24T15:09:41

Adding this answer as I found a hack-ish workaround for the problem.

Turn Identity_Insert On

sqlQuery(myconn, "Set Identity_Insert Employees On", errors = TRUE)

Execute sqlSave query. The catch here is that you need to insert IDs manually which means you're responsible for validating their uniqueness and sequentialness. In my case, I know my table will be empty before my insert so I can just set rownames and addPK to TRUE.

sqlSave(
  myconn, 
  dat=df, 
  tablename = "Employees", 
  append = TRUE, rownames = TRUE, 
  colnames = FALSE, 
  verbose = TRUE, 
  safer = TRUE, 
  addPK = TRUE, 
  fast = TRUE, 
  test = FALSE, 
  nastring = NULL
)

Turn Identity_Insert back off

sqlQuery(myconn, "Set Identity_Insert Employees Off", errors = TRUE)

RODBC Error - Cannot insert explicit value for identity column

1 Answers