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))
    stopifnot(is.character(dbname) && length(dbname) == 1L)
    stopifnot(is.character(table) && length(table) == 1L)
    collapse <- function(x) paste0("'", x, "'", collapse = ",")
    cols <- collapse(names(.data))
    vals <- apply(.data, 1, function(row) paste0("(", collapse(row), ")"))
    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)
    return(sql)
}
sqlInsert(iris[1:5, ], "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'));"

sqlInsert <- function(con, .data, dbname, table, pagesize = 10000) {
    stopifnot(is.data.frame(.data))
    stopifnot(is.character(dbname) && length(dbname) == 1L)
    stopifnot(is.character(table) && length(table) == 1L)
    collapse <- function(x) paste0("'", x, "'", collapse = ",")
    n <- .row_names_info(.data, 2)
    pages <- split(seq_len(n), seq_len(n) %/% (pagesize))
    cols <- collapse(names(.data))
    for (idx in pages) {
        vals <- apply(.data[idx, ], 1, function(row) paste0("(", collapse(row), ")"))
        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)
        cat(sql)
    }
    return(invisible(NULL))
}