问我们如何在SQLServer中批量插入数据而不从RODBC包创建一个文本文件？

EN

library(RODBC)
channel <- ...
dataTable <- ...relevant data...
numberOfThousands <- floor(nrow(dataTable)/1000)
extra <- nrow(dataTable)%%1000

thousandInsertQuery <- function(channel,dat,range){
  sqlQuery(channel,paste0("INSERT INTO Database.dbo.Responses (IDNum,State,Answer)
                                  VALUES "
                          ,paste0(
                            sapply(range,function(k) {
                              paste0("(",dat$IDNum[k],",'",
                                     dat$State[k],"','",
                                     gsub("'","''",dat$Answer[k],fixed=TRUE),"')")
                            })                                         
                            ,collapse=",")))
}

if(numberOfThousands)
  for(n in 1:numberOfThousands)
  {
    thousandInsertQuery(channel,(1000*(n-1)+1):(1000*n),dataTable)
  }
if(extra)
  thousandInsertQuery(channel,(1000*numberOfThousands+1):(1000*numberOfThousands+extra))

library(DBI)
library(RJDBC)
library(tidyverse)

# Specify where you driver lives
drv <- JDBC(
  "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "c:/R/SQL/sqljdbc42.jar") 

# Connect to AWS RDS instance
conn <- drv %>%
  dbConnect(
    host = "jdbc:sqlserver://xxx.ccgqenhjdi18.ap-southeast-2.rds.amazonaws.com",
    user = "xxx",
    password = "********",
    port = 1433,
    dbname= "qlik")

if(0) { # check what the conn object has access to
  queryResults <- conn %>%
    dbGetQuery("select * from information_schema.tables")
}

# Create test data
example_data <- data.frame(animal=c("dog", "cat", "sea cucumber", "sea urchin"),
                           feel=c("furry", "furry", "squishy", "spiny"),
                           weight=c(45, 8, 1.1, 0.8))
# Works in 20ms in my case
system.time(
  conn %>% dbWriteTable(
    "qlik.export.test",
    example_data
  )
)

# Let us see if we see the exported results
conn %>% dbGetQuery("select * FROM qlik.export.test")

# Let's clean the mess and force-close connection at the end of the process
conn %>% dbDisconnect()

require(RODBC)
channel <- #connection parameters
dbPath <- # path to your table, database.table
data <- # the DF you have prepared for insertion, /!\ beware of column names and values types...

# Function to insert 1000 rows of data in one sqlQuery call, coming from
# any DF and into any database.table

insert1000Rows <- function(channel, dbPath, data, range){

    # Defines columns names for the database.table
    columns <- paste(names(data), collapse = ", ")

    # Initialize a string which will incorporate all 1000 rows of values
    values <- ""

    # Not very elegant, but appropriately builds the values (a, b, c...), (d, e, f...) into a string
    for (i in range) {
        for (j in 1:ncol(data)) {

            # First column
            if (j == 1) {

                if (i == min(range)) {
                    # First row, only "("
                    values <- paste0(values, "(")
                } else {
                    # Next rows, ",("
                    values <- paste0(values, ",(")
                }
            }

            # Value Handling
            values <- paste0(
                values

                # Handling NA values you want to insert as NULL values
                , ifelse(is.na(data[i, j])
                    , "null"

                    # Handling numeric values you want to insert as INT
                    , ifelse(is.numeric(data[i, j])
                        , data[i, J]

                        # Else handling as character to insert as VARCHAR
                        , paste0("'", data[i, j], "'")
                    )
                )
            )

            # Separator for columns
            if (j == ncol(data)) {

                # Last column, close parenthesis
                values <- paste0(values, ")")
            } else {

                # Other columns, add comma
                values <- paste0(values, ",")
            }
        }
    }

    # Once the string is built, insert it into SQL Server
    sqlQuery(channel,paste0("insert into ", dbPath, " (", columns, ") values ", values))
}

# Main function which uses the insert1000rows function in a loop
sqlInsertAll <- function(channel, dbPath, data) {
    numberOfThousands <- floor(nrow(data) / 1000)
    extra <- nrow(data) %% 1000
    if (numberOfThousands) {
        for(n in 1:numberOfThousands) {
            insert1000Rows(channel, dbPath, data, (1000 * (n - 1) + 1):(1000 * n))
            print(paste0(n, "/", numberOfThousands))
        }
    }
    if (extra) {
        insert1000Rows(channel, dbPath, data, (1000 * numberOfThousands + 1):(1000 * numberOfThousands + extra))
    }
}