SqlSave in R for creating and saving data in sql table

Question

Hi I am using R to save a data frame to a DB2 SQL table. It seems I can create a table skeleton but not add data to the table -

>df <- read.csv("dat.csv") 

where dat.csv is csv without headers, only raw data in two columns

then i create a table:

 >sqlQuery(channel, "create table sqltable ( col1 int, col2 float )" ( 

where I confirm that the table is created by being able to select the empty "sqltable" table in the database

so now I need to add data from "dat.csv" to "sqltable" by doing:

 >sqlSave(channel, df, "sqltable", verbose=T, fast=T, append=T) no: 1 rownames 1/***/no: 2 col1 31105/***/no: 3 col2 0.001/***/ no: 2 rownames 1/***/no: 2 col1 31106/***/no: 3 col2 0.023/***/ no: 3 rownames 1/***/no: 2 col1 31107/***/no: 3 col2 1.456/***/ no: 4 rownames 1/***/no: 2 col1 31108/***/no: 3 col2 0.001/***/ no: 5 rownames 1/***/no: 2 col1 31109/***/no: 3 col2 2.102/***/ 

everything seems good until i do:

 >sqlQuery(channel,"select * from sqltable") [1] COL1 COL2 <0 rows> or 0-length row.names 

sqlSave command clearly takes data from dat.csv, so why is it not added to the table? what am I doing wrong?

Answer 1

It would be nice to define varTypes in sqlSave. Here is how I do it. :-)

 columnTypes <- list(ColumnName="varchar(255)", NumberColumn="float", datecolumn="date") 

This determines the column types for your data, so you can add an argument to sqlSave. So now let's do sqlSave.

 sqlSave(channel, YourTable, rownames = FALSE, varTypes = columnTypes) 

and if the table has already been added, I would do sqlDrop before that.

Why is this the answer?

Well, I had the same problem. I was getting an error stating that it could not execute sqlSave, but it put my columns in the database. So I had to fix the columns. R tries to do it manually, but it's not perfect. :-)

Answer 2

Use rownames = FALSE inside sqlSave() .

Since you are not providing sample data from dat.csv, I assume that it has 2 columns based on the sqlSave code block. Of your statement, create sqltable has only 2 columns; sqlSave defaults to rownames = TRUE , in which the data you enter from the data block has 3 columns, not 2 that you want. By placing three columns in a Microsoft SQL Server table with two columns without rownames = FALSE my R session terminates with a fatal error.