We want to insert a dataframe into a table in teradata. so we connect to the database
drv = JDBC("com.teradata.jdbc.TeraDriver","C:\\Users\\~\\TeradataJDBCDDriver\\terajdb c4.jar;C:\\Users\\~\\TeradataJDBCDDriver\\tdgssconfig.jar")
conn = dbConnect(drv,"jdbc:teradata://###","username","password")
So we created a dataframe:
column1<-c(1,2,3,4)
column2<-c("bar1","bar2","bar3","bar4")
df=data.frame(column1,column2)
And now we want to write the dataframe to a table in teradata. We tried three approaches
first try where we define the table we want to create with its datatypes
dbWriteTable(conn,"temp.test_table(a int,b varchar(100))", df)
Then the following error appears:
"Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate ([Teradata Database] [TeraJDBC 15.10.00.33] [Error 3706] [SQLState 42000] Syntax error: expected something between ')' and '('.)"
The second approach we create a table first and try to append the dataframe second.
dbSendUpdate(conn,"create table temp.test_table(a int,b varchar(100))")
dbWriteTable(conn,"temp.test_table", df,row.names=F,overwrite=F,append = T)
This leads to the following error:
Error in .local(conn, name, value, ...) : Cannot append to a non-existing table `temp.test_table'
When we check if the table exists using dbListTables() the table does exist however. (but not if we use dbExistTable())
In the third try we try to overwrite instead of append the data.
dbWriteTable(conn,"temp.test_table", df,row.names=F,overwrite=T,append = F)
Which leads to the following error:
Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate ([Teradata Database] [TeraJDBC 15.10.00.33] [Error 3803] [SQLState 42S01] Table 'test_table' already exists.)
now it states that the table already exists (that should be fine as, we want to overwrite it). But the table is not overwritten.
all in all we're looking for a solution to write a dataframe from r into a teradata database.
Anyone knows how to deal with this issue?
