dbAppendTable() error when I try to append data to a local server

Question

I'm just starting my journey with r, so I'm a complete newbie and I can't find anything that will help me solve this.

I have a csv table (random integers in each column) with 9 columns. I read 8 and I want to append them to a sql table with 8 fields (Col1 ... 8, all int's). After uploading the csv into rStudio, it looks right and only has 8 columns:

The code I'm using is:

# Libraries
library(DBI)
library(odbc)
library(tidyverse )

# CSV Files
df = head(
  read_delim(
    "C:/Data/test.txt",
    "   ",
    trim_ws = TRUE,
    skip = 1,
    skip_empty_rows = TRUE,
    col_types = cols('X7'=col_skip())
  )
  , -1
)

# Add Column Headers
col_headings <- c('Col1', 'Col2', 'Col3', 'Col4', 'Col5', 'Col6', 'Col7', 'Col8')
names(df) <- col_headings

# Connect to SQL Server
con <- dbConnect(odbc(), "SQL", timeout = 10)

# Append data
dbAppendTable(conn = con,
              schema = "tmp",
              name = "test",
              value = df,
              row.names = NULL)

I'm getting this error message:

> Error in result_describe_parameters(rs@ptr, fieldDetails) : 
> Query requires '8' params; '18' supplied.

Answer 1

I ran into this issue also. I agree with Hayward Oblad, the dbAppendTable function appears to be finding another table of the same name throwing the error. Our solution was to specify the name parameter as an Id() (from DBI::Id())

So taking your example above:

# Append data
dbAppendTable(conn = con,
              name = Id(schema = "tmp", table = "test"),
              value = df,
              row.names = NULL)

Answer 2

Ran into this issue...

Error in result_describe_parameters(rs@ptr, fieldDetails) : Query requires '6' params; '18' supplied.

when saving to a snowflake database and couldn't find any good information on the error.

Turns out that there was a test schema where the tables within the schema had exactly the same names as in the prod schema. DBI::dbAppendTable() doesn't differentiate the schemas, so until those tables in the text schema got renamed to unique table names, the params error persisted.

Hope this saves someone the 10 hours I spent trying to figure out why DBI was throwing the error.