conn <- odbcDriverConnect(connection ="Driver={SQL Server};Server=servername.com;
                  catalog=Analysis;Uid=login;Pwd=parole")

sqlQuery(conn, paste ("use Analysis; ",
                  "create table Analysis.dbo.ChM_credit_cards (",
                  "id int null, ",
                  "external_id nvarchar(100) null, ",
                  "card_name nvarchar(50) null, ",
                  "user_id bigint null, ",
                  "expiry_status nvarchar(20) null, ",
                  "is_deleted smallint null, ",
                  "expiry_date date null, ",
                  "card_number nvarchar(50) null, ",
                  "is_business smallint null, ",
                  "created_at datetime null, ",
                  "updated_at datetime null, ",
                  "saved_email nvarchar(100) null, ",
                  "holder_name nvarchar(100) null, ",
                  "activated smallint null, ",
                  "identifier_id bigint null)"))

nr <- nrow(crcard)

for (i in 1:nr)
{
  id_crc            <- crcard$id[i]
  external_id_crc   <- crcard$external_id[i]
  card_name_crc     <- crcard$name[i]
  user_id_crc       <- crcard$user_id[i]
  expiry_status_crc <- crcard$expiry_status[i]
  is_deleted_crc    <- crcard$is_deleted[i]
  expiry_date_crc   <- as.character(crcard$expiry_date[i])
  card_number_crc   <- crcard$number[i]
  is_business_crc   <- crcard$is_business[i]
  created_at_crc    <- as.character(crcard$created_at[i])
  updated_at_crc    <- as.character(crcard$updated_at[i])
  email_crc         <- crcard$saved_email[i]
  holdername_crc    <- crcard$holder_name[i]
  activated_crc     <- crcard$activated[i]
  identifier_id_crc <- ifelse(is.na(crcard$identifier_id[i])==TRUE, 0, crcard$identifier_id[i])


  sqlQuery(conn, paste ("use Analysis; ",
                    "insert into Analysis.dbo.ChM_credit_cards ",
                    "(id , external_id, card_name, ",
                    "user_id, expiry_status, is_deleted, ",
                    "expiry_date, card_number, is_business, ",
                    "created_at, updated_at, saved_email, ",
                    "holder_name, activated, identifier_id)",
                    "values (",
                    id_crc,", '",
                    external_id_crc,"', '",
                    card_name_crc, "', ",
                    user_id_crc, ", '",
                    expiry_status_crc, "', ",
                    is_deleted_crc, ", '",
                    expiry_date_crc, "', '",
                    card_number_crc, "', ",
                    is_business_crc, ", '",
                    created_at_crc,"', '",
                    updated_at_crc,"', '",
                    email_crc, "', '",
                    holdername_crc, "', ",
                    activated_crc, ", ",
                    identifier_id_crc, ")"))

sqlInsert <- function(.data, dbname, table) {
    stopifnot(is.data.frame(.data))
    cols <- paste0("`", names(.data), "`", collapse = ",")
    vals <- apply(.data, 1, function(row) paste0("'", row, "'", collapse = ","))
    vals <- paste(vals, collapse = ",")
    vals <- gsub("'NA'", "NULL", vals, fixed = TRUE)
    sql <- sprintf("USE %s; INSERT INFO %s.%s(%s) values(%s)",
                    dbname, dbname, table, cols, vals)
    sql
}
sqlInsert(iris[1:10, ], "analytics", "iris")
#> [1] "USE analytics; INSERT INFO analytics.iris(`Sepal.Length`,`Sepal.Width`,`Petal.Length`,`Petal.Width`,`Species`) values('5.1','3.5','1.4','0.2','setosa','4.9','3.0','1.4','0.2','setosa','4.7','3.2','1.3','0.2','setosa','4.6','3.1','1.5','0.2','setosa','5.0','3.6','1.4','0.2','setosa','5.4','3.9','1.7','0.4','setosa','4.6','3.4','1.4','0.3','setosa','5.0','3.4','1.5','0.2','setosa','4.4','2.9','1.4','0.2','setosa','4.9','3.1','1.5','0.1','setosa')"