Unable to create table more than 24 columns SQL-Lite in R

[karthees]

setwd("E:/")

library("RSQLite")

db <- dbConnect(SQLite(),dbname="chinook.db")

dbCreateTable(db, "Newtable", churn_data_raw)

Error in vapply(fields, function(x) DBI::dbDataType(con, x), character(1)) : **
** values must be length 1,
** but FUN(X[[24]]) result is length 2**

[mishabalyasin]

You can definitely create tables with more than 24 columns, there is just something wrong with the name of the column you have. Without reprex it's impossible to say what's the problem, but here is the example of how you can prepare one + confirmation that it's possible to create a table with 66 columns:

library("RSQLite")
library(magrittr)

db <- dbConnect(SQLite(), dbname="chinook.db")

mtcars_many_columns <- cbind(mtcars, mtcars, mtcars, mtcars, mtcars, mtcars) %>%
  tibble::as_tibble(.name_repair = "unique")
#> New names:
#> * mpg -> mpg...1
#> * cyl -> cyl...2
#> * disp -> disp...3
#> * hp -> hp...4
#> * drat -> drat...5
#> * … and 61 more problems

dbCreateTable(db, "Newtable3", mtcars_many_columns, temporary = TRUE)

res <- dplyr::tbl(src = db, "Newtable3")

res
#> # Source:   table<Newtable3> [?? x 66]
#> # Database: sqlite 3.29.0 [chinook.db]
#> # … with 66 variables: mpg...1 <dbl>, cyl...2 <dbl>, disp...3 <dbl>,
#> #   hp...4 <dbl>, drat...5 <dbl>, wt...6 <dbl>, qsec...7 <dbl>,
#> #   vs...8 <dbl>, am...9 <dbl>, gear...10 <dbl>, carb...11 <dbl>,
#> #   mpg...12 <dbl>, cyl...13 <dbl>, disp...14 <dbl>, hp...15 <dbl>,
#> #   drat...16 <dbl>, wt...17 <dbl>, qsec...18 <dbl>, vs...19 <dbl>,
#> #   am...20 <dbl>, gear...21 <dbl>, carb...22 <dbl>, mpg...23 <dbl>,
#> #   cyl...24 <dbl>, disp...25 <dbl>, hp...26 <dbl>, drat...27 <dbl>,
#> #   wt...28 <dbl>, qsec...29 <dbl>, vs...30 <dbl>, am...31 <dbl>,
#> #   gear...32 <dbl>, carb...33 <dbl>, mpg...34 <dbl>, cyl...35 <dbl>,
#> #   disp...36 <dbl>, hp...37 <dbl>, drat...38 <dbl>, wt...39 <dbl>,
#> #   qsec...40 <dbl>, vs...41 <dbl>, am...42 <dbl>, gear...43 <dbl>,
#> #   carb...44 <dbl>, mpg...45 <dbl>, cyl...46 <dbl>, disp...47 <dbl>,
#> #   hp...48 <dbl>, drat...49 <dbl>, wt...50 <dbl>, qsec...51 <dbl>,
#> #   vs...52 <dbl>, am...53 <dbl>, gear...54 <dbl>, carb...55 <dbl>,
#> #   mpg...56 <dbl>, cyl...57 <dbl>, disp...58 <dbl>, hp...59 <dbl>,
#> #   drat...60 <dbl>, wt...61 <dbl>, qsec...62 <dbl>, vs...63 <dbl>,
#> #   am...64 <dbl>, gear...65 <dbl>, carb...66 <dbl>

^{Created on 2019-08-22 by the reprex package (v0.3.0)}

[karthees]

@mishabalyasin

library("RSQLite") library(magrittr) db <- dbConnect(SQLite(), dbname="chinook.db") mtcars_many_columns <- cbind(mtcars, mtcars, mtcars, mtcars, mtcars, mtcars) %>% tibble::as_tibble(.name_repair = "unique") #> New names: #> * mpg -> mpg...1 #> * cyl -> cyl...2 #> * disp -> disp...3 #> * hp -> hp...4 #> * drat -> drat...5 #> * … and 61 more problems dbCreateTable(db, "Newtable3", mtcars_many_columns, temporary = TRUE) res <- dplyr::tbl(src = db, "Newtable3") res

Thanks mishabalyasin