0

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SCDCE
  • 1,603
  • 1
  • 15
  • 28

2 Answers2

2

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.

SCDCE
  • 1,603
  • 1
  • 15
  • 28
Benjamin
  • 16,897
  • 6
  • 45
  • 65
  • I can't get any version of this to work, why would I want to rep the SQL statement for every column? – SCDCE Jun 13 '18 at 15:35
  • My apologies, I misplaced a parenthesis. `query` should be a length one character vector. I've fixed it in the answer. – Benjamin Jun 13 '18 at 15:44
  • 42000 -3502 [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. [RODBCext] Error: SQLExecute failed... Not enough storage on the server? Don't have write access? – SCDCE Jun 13 '18 at 15:56
  • `ODBC Microsoft Access Driver` that's a curious part of the error that I didn't catch before. I've never used that driver with SQL Server. How are you forming your connection? – Benjamin Jun 13 '18 at 16:01
  • RODBC::odbcConnectAccess(), I need to connect directly to the server, don't I? My frontend is via Access, backend is a SQL database – SCDCE Jun 13 '18 at 16:14
  • If you can connect directly to SQL Server, I would strongly recommend it. – Benjamin Jun 13 '18 at 16:15
  • Was never able to write directly to the SQL server but your code worked a charm for an Access table I was using for an Excel import, thanks! – SCDCE Jun 14 '18 at 12:37
-2

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
bitsNbytes
  • 64
  • 6