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, Apr 23 '15 at 20:58
@MrFlick yes, in which case I get the error "length of 'dimnames' [2] not equal to array extent". [This Post](http://stackoverflow.com/questions/6353644/how-to-skip-primary-key-in-sqlsave-command) is very similar, but the answer doesn't really help. — Ben, Apr 23 '15 at 21:01

Ben · Answer 1 · 2020-10-28T20:04:49.093

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 Answers1