[R] Package for converting R datasets into SQL Server (create table and insert statements)?

[David Winsemius]

On Oct 9, 2010, at 9:02 AM, johannes rara wrote:

> Thanks, but I'm not looking for a function to save dataframes into a
> RDBMS. I'm looking for a function which creates CREATE TABLE and
> INSERT statements from a dataframe.

(My first comment is speculation that Eric was intending that you look  
at the _code_ of sqlSave rather than on its output. My reading of the  
code (at the console rather than the source) suggests that it is  
constructing the code and passing it to the external drivers.)

Looking at the web documentation linked from sqldf()'s help page, it  
appears that at least part of this could also addressed by example 9  
of the current full documentation:

http://code.google.com/p/sqldf/

BOD is a built-in dataframe:

require(sqldf)
?sqldf

# Portion of example 9:

 > sqldf("pragma table_info(BOD)")
   cid   name type notnull dflt_value pk
1   0   Time REAL       0       <NA>  0
2   1 demand REAL       0       <NA>  0
 >
 > sqldf(c("select * from BOD", "select * from sqlite_master"))
    type name tbl_name rootpage
1 table  BOD      BOD        2
                                                      sql
1 CREATE TABLE `BOD` \n( "Time" REAL,\n\tdemand REAL \n)

There is integration with a variety of SQL db's, although the act of  
table creation may be limited to SQLite, since the primary advertised  
activity is SELECT statements and it does its access through the  
SQLite drive in memory  ... at least as I understand it.

-- 
David.

>
> -J
>
> 2010/10/5 Eric Lecoutre <ericlecoutre at gmail.com>:
>> Hi,
>>
>> You can have a look at RODBC and its function sqlSave.
>>
>> HTH,
>>
>> Eric
>>
>>
>> 2010/10/3 johannes rara <johannesraja at gmail.com>
>>>
>>> Hi,
>>>
>>> R contains many good datasets which would be valuable in other
>>> platforms as well. My intention is to use R datasets on SQL Server  
>>> as
>>> a sample tables. Is there a package that would do automatic  
>>> conversion
>>> from the dataset "schema" into a SQL Server CREATE TABLE statement
>>> (and INSERT INTO statements)?
>>>
>>> For example.
>>>
>>>> str(cars)
>>> 'data.frame':   50 obs. of  2 variables:
>>>  $ speed: num  4 4 7 7 8 9 10 10 10 11 ...
>>>  $ dist : num  2 10 4 22 16 10 18 26 34 17 ...
>>>>
>>>
>>> would become
>>>
>>> create table dbo.cars (
>>>              id int identity(1,1) not null,
>>>              speed int not null,
>>>              dist int not null,
>>>              constraint PK_id primary key clustered (id ASC)
>>>              on [PRIMARY]
>>>              )
>>>
>>> insert into dbo.cars
>>>    values (N'4', N'2'),
>>>              (N'4', N'10'),
>>>              (N'7', N'4'),
>>>               etc.
>>>
>>> -J
>>>
>>> ______________________________________________
>>> R-help at r-project.org mailing list
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide
>>> http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>
>>
>>
>> --
>> Eric Lecoutre
>> Consultant - Business & Decision
>> Business Intelligence & Customer Intelligence
>>
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

David Winsemius, MD
West Hartford, CT