Вы можете использовать функцию, как показано ниже, это зависит от пакета RODBC. Но вы можете изменить его по своему усмотрению.
sqlInsertBulk <- function(data, table, connString, chunksize = 1000)
{
stopifnot(chunksize <= 1000)
dbhandle <- odbcDriverConnect(connString)
nrow_initial<-sqlQuery(dbhandle,paste("SELECT COUNT (1) FROM ",table))
#If data includes Inf value, stop function.
numericCols <- names(data)[sapply(data, is.numeric)]
if (length(numericCols != 0)) {
if(sum(unlist(data[,lapply(.SD, function(x) any(x == Inf)),.SDcols = numericCols]),na.rm=T)>0){
stop("Data includes Inf value.")
}
}
chunknumber <- ceiling(nrow(data)/chunksize)
qstart <- paste("INSERT INTO ", table ," ( ",paste(colnames(data),collapse = ", "), " ) VALUES")
for(chunki in 1:chunknumber)
{
chunkstart <- 1 + chunksize * ( chunki - 1 )
chunkend <- min(nrow(data), chunki * chunksize )
chunkdata <- data[chunkstart:chunkend]
valuestring <- vector(mode="character", length=chunkend - chunkstart + 1 )
for(i in 1:nrow(chunkdata)){
valuestring[i] <- paste("(", paste(sapply(chunkdata[i], function(input){
if(!class(input) %in% c("numeric", "integer")) {
input<-paste0("'", input, "'")
}
if(is.na(input))
{
input<-"NULL"
}
return (input)
}), collapse=", "), ")")
}
qend <- paste(valuestring, collapse = ", ")
q <- paste(qstart, qend)
print(paste("Chunk", chunki, "is in process."))
up <- sqlQuery(dbhandle,q)
if(length(up) > 0){
stop(up)
}
print(paste("Chunk", chunki, "is uploaded."))
}
nrow_final <- sqlQuery(dbhandle,paste("SELECT COUNT (1) FROM ",table))
odbcClose(dbhandle)
if(nrow_final-nrow_initial==nrow(data)) {
print(paste("All ",nrow(data)," data is uploaded."))
return(T)
} else {
print(paste0("Warning!!! Only ",nrow_final-nrow_initial, " out of ",nrow(data), " are uploaded." ))
return(F)
}
}
person
Sabri Karagönen
schedule
01.11.2017