Is it possible to export dataframes to SQL Server ?

[Fabian_CO]

I'm connected to SQL Server with Rstudio and I would like to export several R dataframes to a database. But I don't know how to proceed...

Anyone has an idea ? An example ?

Many thanks in advance for your assistance,

[jrkrideau]

I have never used SQL Server but there seems to be a lot of information on the internet. would something like this be of any help?

[andresrcs]

You can use dbCreateTable(), dbWriteTable() and dbAppendTable()

Copy data frames to database tables — dbWriteTable

[Fabian_CO]

Thanks @andresrcs, I will try this !

[Fabian_CO]

It works with the example dataset mtcars but not with mine :

dbWriteTable(con,"ci_diplomes", ci_diplomes)
Error in result_insert_dataframe(rs@ptr, values, batch_rows) : 
  nanodbc/nanodbc.cpp:1655: 22001: [Microsoft][ODBC SQL Server Driver]Troncation à droite de la chaîne de données 

I will do so more digging !

[Fabian_CO]

I guess the problem comes from the type of columns (which are characters). I also guess that i need to use field.types...

[Fabian_CO]

I try this, but it does not work :

dbWriteTable(con, name = "ci_diplomes", value = ci_diplomes, field.types="varchar(6000)")

[Fabian_CO]

New try with this one :

dbWriteTable(con, 
             name = "ci_diplomes", 
             value = ci_diplomes, 
             field.types = c(`code-diplome`="varchar(3000)",
             `code-type-diplome`="varchar(3000)",
             `code-niveau-europeen`="varchar(3000)",
             `code-rncp`="varchar(3000)",
             `code-scolarite`="varchar(3000)",
             `code-sise`="varchar(3000)",
             `code-onisep`="varchar(3000)",
             `code-onisep-ideo`="varchar(3000)",
             `code-specificite-ideo`="varchar(3000)",
             `code-cqp`="varchar(3000)",
             `code-ministere-emploi`="varchar(3000)",
             `code-afpa`="varchar(3000)",
             `code-nsf`="varchar(3000)",
             `code-lettre-nsf`="varchar(3000)",
             `code-carif-07`="varchar(3000)",
             `code-carif-26`="varchar(3000)",
             `inscrit-rncp`="varchar(3000)",
             `type-sigle`="varchar(3000)",
             `type-complet`="varchar(3000)",
             `intitule-diplome`="varchar(3000)",
             `dominante`="varchar(3000)",
             `mention`="varchar(3000)",
             `specialite`="varchar(3000)",
             `intitule-sigle`="varchar(3000)",
             `code-niveau-entree`="varchar(3000)",
             `accessibilite-fi`="varchar(3000)",
             `accessibilite-ca`="varchar(3000)",
             `accessibilite-fc`="varchar(3000)",
             `accessibilite-cp`="varchar(3000)",
             `accessibilite-vae`="varchar(3000)",
             `accessibilite-ind`="varchar(3000)",
             `accessibilite-uc`="varchar(3000)",
             `valideur`="varchar(3000)",
             `annee-premiere-session`="varchar(3000)",
             `annee-derniere-session`="varchar(3000)",
             `code-apogee`="varchar(3000)",
             `etat`="varchar(3000)",
             `date-creation`="varchar(3000)",
             `date-maj`="varchar(3000)",
             `validation-interne`="varchar(3000)",
             `validation-alfa`="varchar(3000)")
             )

Still does not work :

Error in result_insert_dataframe(rs@ptr, values, batch_rows) : 
  nanodbc/nanodbc.cpp:1655: 22001: [Microsoft][ODBC SQL Server Driver]Troncation à droite de la chaîne de données

[andresrcs]

You could increase your chances of getting help if you provide a small reproducible example that someone with access to a Microsoft SQL server can try, especially sample data on a copy/paste friendly format.

If you have stored the data set in some R object, the dput() function is very handy.

[Fabian_CO]

I finally found this answer : I should have written varchar(max) in the fiels.types thing for each column.

Thanks a lot for your time !