R/sqlInsertUnique.R

In gogonzo/oddsandsods: Miscellanous functions

sqlInsertUnique <- function( df_new, table_name, by = "id"){

  df_new[df_new==""] <- NA
  dbSendQuery(con, "DROP TABLE IF EXISTS tempTable")
  df_new %>% oddsandsods:::sqlCreateTable(table_name = "tempTable")
  df_new %>% sqlInsert("tempTable")

  by_cols <- paste( by, collapse=",")
  cols <-  paste( colnames(df_new), collapse="," )

  query <-
    sprintf({"
    INSERT INTO %s (%s)
    SELECT * FROM
    (
      select *
      from tempTable
      where concat( %s ) not in
        (select distinct concat( %s )
         from %s )
    ) x"},
    table_name ,
    cols ,
    paste( by , collapse="," ) ,
    paste( by , collapse="," ) ,
    table_name
  ) %>% cat

}