How to add data to a SQL Server table using an IDENTITY primary key using the sqlSave () function in R?

Question

I created the table on SQL Server as follows:

CREATE TABLE testPK 
(
    ID INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
    NumVal NUMERIC (18, 4)
)

Now I want to add data to testPK from the R program using the RODBC function sqlSave(), as shown below:

# Specify data to append
test.dt <- data.table(NumVal = 1.0)

# Assign connection
myconn <- odbcDriverConnect(connectionString)

# Append test.dt to SQL table testPK
sqlSave(channel = myconn, dat = test.dt, tablename = 'testPK',
        rownames = FALSE, append = TRUE)

# Close connection
odbcCloseAll()

However, this returns an error message

Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  : 
  missing columns in 'data'

I did not specify a value for the column identifier in my data table because I assume that the IDENTITY specification in this column of my SQL table causes SQL Server to generate a unique value when adding a new record. How can I achieve this result from R?

The same question was sent here , but without a decision.

Answer 1

sqlSave(), SQL. :

# Specify data to append
test.dt <- data.table(NumVal = 1.0)

# Assign connection
myconn <- odbcDriverConnect(connectionString)

# Concatenate the VALUES portion of the query
values <- paste("(", test.dt$NumVal, ")", sep = "", collapse = ",")

# Create the full query
testQuery <- paste("INSERT INTO testPK (NumVal) VALUES", values)

# Append test.dt to SQL table testPK
sqlQuery(channel = myconn, query = testQuery)

# Close connection
odbcCloseAll()