Create SQL from column values in a data frame

This document quickly demonstrates one way to use a data.frame values to easily create SQL scripts

The two data frames are:

kable(df)

table	emp_num	emp_name	income
emp	1	prabhu	1000
emp	2	ram	2000
emp	3	prasaath	3003

kable(table_str)

table	column
emp	emp_name
emp	income

Let’s first use df to create an INSERT SQL.

df %>%
  mutate_if(is.factor, as.character) %>%
  ddply(.(emp_num), function(x) {
    paste(
      "INSERT INTO",
      names(x)[1] ,
      "(",
      paste(names(x)[2:length(x)], collapse = ","),
      ") VALUES (",
      paste0("'", x[, 2:length(x)], "'", collapse = ","),
      ");"
    )
  })

##   emp_num
## 1       1
## 2       2
## 3       3
##                                                                                V1
## 1   INSERT INTO table ( emp_num,emp_name,income ) VALUES ( '1','prabhu','1000' );
## 2      INSERT INTO table ( emp_num,emp_name,income ) VALUES ( '2','ram','2000' );
## 3 INSERT INTO table ( emp_num,emp_name,income ) VALUES ( '3','prasaath','3003' );

Next we will create some kind of data validation script that will create SQL queries for row count when column in NULL.

table_str %>%
  mutate_if(is.factor, as.character) %>%
  ddply(
    .(table, column),
    summarise,
    query1 = paste("select count(1) from", table, "where", column, "is NULL;")
  )

##   table   column                                           query1
## 1   emp emp_name select count(1) from emp where emp_name is NULL;
## 2   emp   income   select count(1) from emp where income is NULL;