search

ankane / dbx

A fast, easy-to-use database library for R
Other
187 stars 15 forks source link

Insert not working for Teradata #6

Closed happyshows closed 5 years ago

happyshows commented 6 years ago

dbxSelect is fine though.

> dbxInsert(con,table,insert,batch_size = 1000)
Error in new_result(connection@ptr, statement) : 
  nanodbc/nanodbc.cpp:1344: 42000: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between ')' and ','.
ankane commented 6 years ago

Hey @happyshows, can you turn on logging and paste that output as well?

happyshows commented 6 years ago

I just tried to insert head(mtcars) into a new table

Error in new_result(connection@ptr, statement) : 
  nanodbc/nanodbc.cpp:1344: 42000: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between ')' and ','.  
(126.6ms) INSERT INTO "MYTABLE" ("mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb") VALUES (21, 6, 160, 110, 3.9, 2.62, 16.46, 0, 1, 4, 4), (21, 6, 160, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4), (22.8, 4, 108, 93, 3.85, 2.32, 18.61, 1, 1, 4, 1), (21.4, 6, 258, 110, 3.08, 3.215, 19.44, 1, 0, 3, 1), (18.7, 8, 360, 175, 3.15, 3.44, 17.02, 0, 0, 3, 2), (18.1, 6, 225, 105, 2.76, 3.46, 20.22, 1, 0, 3, 1), (14.3, 8, 360, 245, 3.21, 3.57, 15.84, 0, 0, 3, 4), (24.4, 4, 146.7, 62, 3.69, 3.19, 20, 1, 0, 4, 2), (22.8, 4, 140.8, 95, 3.92, 3.15, 22.9, 1, 0, 4, 2), (19.2, 6, 167.6, 123, 3.92, 3.44, 18.3, 1, 0, 4, 4)
ankane commented 6 years ago

It looks like Teradata doesn't support multiple row insert syntax. Try passing batch_size=1 to the method.

ankane commented 5 years ago

Closing due to no response