How to insert a single row R data.frame into a SQL Server database?

0
votes

I'm struggling to append a new row of data into an existing SQL Server database.

These:

sqlQuery(con, paste("INSERT INTO df1 SELECT * FROM df2"))

sqlQuery(con, paste("INSERT INTO df1 SELECT * FROM ", df2))

sqlQuery(con, paste("INSERT INTO df1 SELECT * FROM ", sqldf("SELECT * FROM df2")))

All produce an error:

[1] "42S02 -1305 [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'col_1_val'. Make sure it exists and that its name is spelled correctly."

[2] "[RODBC] ERROR: Could not SQLExecDirect 'INSERT INTO df1 SELECT * FROM col_1_val'"

col_1_val is the first column value in df2

Going the sqlSave route:

sqlSave(con, df2, tablename = "df1", append = TRUE, rownames = FALSE, colnames = FALSE)

throws a memory allocation error:

Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : 'Calloc' could not allocate memory (1073741824 of 1 bytes)

2
sqlrsql-serverrodbc
Are the tables df1 and df2 existing? – Marcel Gangwisch
Well, at least you need an additional ) at the end of each. – arg0naut91
df1 = existing SQL Server database and df2 = existing R data.frame – SCDCE
Possible duplicate of How to insert a dataframe into a SQL Server table? – iamdave
iamdave, I saw that answer before I posted this, do I just have to list all values in df1 and df2? – SCDCE

2 Answers

2
votes

On the assumption that the names and the order of columns in df1 and df2 are identical, this should work:

query <- 
  paste0("INSERT INTO df1 ",
         "(", paste0(names(df2), collapse = ", "), ") ",
         "VALUES (",
         paste0(rep("?", length(df2)), collapse = ", "), ")")

library(RODBCext)

sqlExecute(con, 
           query,
           data = df2)

The query that is written looks like this, when using mtcars as df1.

"INSERT INTO df1 (mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb) VALUES ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?"

This permits you to generate the statement that adds all of column names to the query without having to manually declare them. Using sqlExecute invokes a parameterized query. The question marks are then bound to your data and then executed as part of the statement.

-2
votes

TRY

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

if you want columns from table 2 

INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT  table2.column1, table2.column2, 8, 'some string etc.'
FROM    table2