I am trying to pass a SQL insert query from R to a SQL Server database.

query <- "INSERT INTO table1 SELECT * FROM table2 WHERE UPRN NOT IN (SELECT UPRN FROM table1 ) AND CHANGE_CODE = 'I'"
result <- sqlQuery(channel, query, errors = TRUE)

The result shows that an erroneous single quote has appeared in this part CHANGE_CODE = 'I'"

As CHANGE_CODE = 'I''"

Any ideas why this is happening and how to avoid it?

The actual error returned is

“[RODBC] ERROR: Could not SQLExecDirect ‘INSERT INTO table1 SELECT * FROM table2 WHERE UPRN NOT IN (SELECT UPRN FROM table1) AND CHANGE_CODE = ‘I””

I have managed to get around the problem by using two queries as follows (not ideal as hoped to only use sqlQuery):

data_to_insert <- sqlQuery(channel, "SELECT * FROM table2 WHERE UPRN NOT IN (SELECT UPRN FROM table1) AND CHANGE_CODE = 'I'")
result3 <-sqlSave(channel, data_to_insert, tablename = "table1", append = TRUE, rownames = FALSE)