Curious Curious - 26 days ago 9
R Question

Importing data frame from R to SQL server using sqlSave() but column truncated to 255 bytes?

I have a data frame with multiple columns exceeding 255 characters, when I try to import the data frame to sql server using the code below, multiple columns get truncated to 255 characters.

myconn <- odbcDriverConnect("db_name")
sqlSave(myconn, my_data, tablename = "Table_1" ,rownames=FALSE, append = TRUE)


Things I tried but didn't work:

I tried to create the table in sql before importing the data from R but that didn't work:

CREATE TABLE TEST_Table
(
[Column_1] nvarchar(max),
[Column_2] nvarchar(max),
[Column_3] nvarchar(max)
);


I also tried to specify
varTypes
as below before importing the data:

columnTypes <- list(Column_1 = "nvarchar(max)", Column_2 = "nvarchar(max)", Column_3 = "nvarchar(max)")
sqlSave(myconn, my_data, tablename = "Table_1" ,rownames=FALSE, append = TRUE, varTypes=columnTypes)


What else can be done to write this data frame to the database as a table without truncating the character strings?

Answer Source

An alternative solution is

library (RODBCext)
channel <- odbcConnect (...)

sqlExecute (channel,
          "INSERT INTO Table_1
           (Column_1, Column_2, Column_3)
           VALUES (?,?,?)",
           data = df [c ("Column_1", "Column_2", "Column_3")])