#' @export
sqlAppendTable <- function(df_new, table_name, by="id"){
# df_new <- read.table("~/debug_file_speedway_dashboard")
df_new[df_new==""] <- NA
dbSendQuery(con, "DROP TABLE IF EXISTS tempTable")
df_new %>% oddsandsods:::sqlCreateTable(table_name = "tempTable")
df_new %>% sqlInsert("tempTable")
#
if("id" %in% colnames(df_new)){
fields_to_insert <- setdiff(colnames(df_new),"id")
fields_to_insert <- paste0("new.",fields_to_insert) %>%
paste(collapse=",\n")
fields <- setdiff(colnames(df_new),"id") %>% paste(collapse=",")
} else {
fields_to_insert <- paste0("new.",colnames(df_new)) %>% paste(collapse=",")
fields <- colnames(df_new) %>% paste(collapse=",")
}
fields_to_match <- paste0(paste0("old.",by),"=",paste0("new.",by)) %>%
paste(collapse=" and ")
fields_to_update <- setdiff(colnames(df_new), by)
fields_to_update <- paste0(
paste0("old.",fields_to_update),"=",
paste0("new.",fields_to_update)
) %>%
paste(collapse=",\n")
#
update_cmd <- sprintf({"
UPDATE %1$s old
INNER JOIN tempTable new
ON %2$s
SET
%3$s,
old.updated_at = CURRENT_TIMESTAMP;"}, table_name, fields_to_match, fields_to_update)
insert_cmd <- sprintf({"
INSERT INTO %1$s (%2$s, updated_at)
SELECT %3$s, CURRENT_TIMESTAMP
FROM tempTable new
LEFT JOIN %1$s old ON %4$s
WHERE old.id IS NULL"}, table_name, fields, fields_to_insert, fields_to_match)
dbSendQuery(con, update_cmd); dbClearResult(dbListResults(con)[[1]])
dbSendQuery(con, insert_cmd); dbClearResult(dbListResults(con)[[1]])
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.