#' Bulk insert
#'
#' Bulk insert to database
#' @param df data frame to be inserted into database
#' @param table_name name of table within connection
#' @param loop specifies if each row will be inserted seperately. If FALSE all values will be inserted at once. Default = T
#' @examples
#' oddsandsods:::sqlCreateTable(iris, "iris")
#' iris2 <- iris[sample(1:nrow(iris),30000, replace=T), ]
#'
#' sqlInsert(iris2,
#' table_name = "iris",
#' loop = F)
#' SQLInsertSplit(iris2,
#' table_name = "iris",
#' chunk_size = 10000)
#'
#' @export
sqlInsert <- function(df, table_name,loop=F, ignore=FALSE){
# df = iris[1:5,]; df[1,1] <- NA; df[4,5] <- NA; df[2,2] <- "" ;df$id = 1:nrow(df)
colnames(df) %<>% gsub("[[:punct:]]","_",x=.)
colClasses <- lapply(df,class)
FIELDS <- names(colClasses) %>% paste(collapse=", ")
if(ignore){
query <- sprintf("INSERT IGNORE INTO %1$s (%2$s) VALUES VALS", table_name, FIELDS)
} else {
query <- sprintf("INSERT INTO %1$s (%2$s) VALUES VALS", table_name, FIELDS)
}
for(i in 1:ncol(df)){
if(any(colClasses[[i]] %in% c('character','factor','Date','POSIXct','POSIXt','POSIXlt')))
df[[i]] <-
df[[i]] %>% gsub("\'","",x=.) %>% oddsandsods::quoted() %>% gsub("\'NA\'|\'\'","NULL",x=.)
if(any(colClasses[[i]] %in% c('numeric','integer','logical')))
if(any(!is.finite(df[[i]])))
df[[i]] %<>% as.character %>% car:::recode("NA='NULL'")
}
VALS <- apply(df, 1, function(x) paste("(",paste(x,collapse=","),")"))
if(!loop) VALS %<>% paste(collapse = ",")
for(i in 1:length(VALS))
dbSendQuery(con,gsub("VALS",VALS[i], query))
dbClearResult(dbListResults(con)[[1]])
}
#' Bulk insert
#'
#' Bulk insert to database
#' @param df data frame to be inserted into database
#' @param table_name name of table within connection
#' @param loop specifies if each row will be inserted seperately. If FALSE all values will be inserted at once. Default = T
#' @examples
#' sqlCreateTable(iris)
#' iris2 <- iris[sample(1:nrow(iris),30000, replace=T), ]
#'
#' sqlInsert(iris2,
#' table_name = "iris",
#' loop = F)
#' SQLInsertSplit(iris2,
#' table_name = "iris",
#' chunk_size = 10000)
#'
#' @export
SQLInsertSplit <- function(data, table, chunk_size = 10000, ignore = FALSE){
idx_1 <- seq(1, nrow(data), chunk_size)
idx_2 <- c( idx_1[-1], nrow(data) )
for(i in 1:length(idx_1))
sqlInsert( data[idx_1[i]:idx_2[i],] , table, ignore=ignore )
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.