How to specify vartype for sqlSave() for multiple columns without manually typing in R?

Some reproducible code. Note that your database and server name may be different.

library(RODBC)

iris <- iris

connection <- odbcDriverConnect(
  "Driver={SQL Server};
   Server=localhost\SQLEXPRESS;
   Database=testdb;
   Trusted_connection=true;"
)

# create table in sql and move dataframe values in
columnTypes <- list( 
                Sepal.Length="decimal(28,0)",
                Sepal.Width="decimal(28,0)",
                Petal.Length ="decimal(28,0)",
                Petal.Width ="decimal(28,0)",
                Species = "varchar(255)"
                )

sqlSave(connection,iris,varTypes = columnTypes)

This is how i export a dataframe to sql management studio as a table and it works but, say I have one hundred new columns in iris. Do I have to specify each column name = to decimal(28,0) in my columnTypes variable?

# but what if i have
iris$random1 <- rnorm(150)
iris$random2 <- rnorm(150)
iris$random3-999 ..... <- rnorm(150) ....
iris$random1000 <- rnorm(150)

By default the columns go in as floats as least in my actual dataframe (iris is just the example), so that’s why I need to manually change them in columnTypes. I want everything after the 5 original columns in iris to be decimal(28,0) format without manually including them in columnTypes.

Answer

I did not read into the sqlSave() statement and possible alternatives. Meaning: there might be a more apropriate solution. Anyhow you can generate the list of wanted definitions in base R by repitition and combining lists:

# dummy data
df <- data.frame(Sepal.Lengt = 1:5,Sepal.Width = 1:5,Petal.Length = 1:5,Petal.Width = 1:5,Species = 1:5,col6 = 1:5,col7 = 1:5)

# all column names after the 5th -> if less then 6 columns you will get an error here!
vec <- names(df)[6:ncol(df)]

# generate list with same definition for all columns after the 5th
list_after_5 <- as.list(rep("decimal(28,0)", length(vec)))

# name the list items acording to the columns after the 5th
names(list_after_5) <- vec

# frist 5 column definitions manualy plus remaining columns with same definition from list generate above
columnTypes <- c(list(Sepal.Length="decimal(28,0)",
                      Sepal.Width="decimal(28,0)",
                      Petal.Length ="decimal(28,0)",
                      Petal.Width ="decimal(28,0)",
                      Species = "varchar(255)"),
                 list_after_5)

columnTypes 

$Sepal.Length
[1] "decimal(28,0)"

$Sepal.Width
[1] "decimal(28,0)"

$Petal.Length
[1] "decimal(28,0)"

$Petal.Width
[1] "decimal(28,0)"

$Species
[1] "varchar(255)"

$col6
[1] "decimal(28,0)"

$col7
[1] "decimal(28,0)"

Since you example code seems to be working for you, this should also though (judging by the output) – I did not test it with a DB, as I have no test setup available atm.