Creating a SQL structure table in R using contents of a dataframe

Question

#   Create dummy dataframe
dataframe1 <- rbind("a","b","c")

#   Create variable list 
variablelist <- unique(dataframe1[,1])

#   Loop through adding VARCHAR and commas
sql_var_list <- NULL

for(char in seq_along(variablelist)){
  sql_var_list <- paste(sql_var_list,client_variables[char]," VARCHAR,",sep="")
}

#   Remove the final comma
sql_var_list <- substr(sql_var_list, 1, nchar(sql_var_list)-1)

#   Create structure table SQL string
create_strture_table <- paste("CREATE TABLE (",sql_var_list,")",sep="")

In the above code I'm using the contents from a column in a dataframe in R to create a structure table that will later be created in redshift. The above code is working, but my method seems a bit untidy, as I'm new to R I wonder if someone can suggest a better approach?

Don't need any loops or additional variables, just do paste0("CREATE TABLE (", paste(dataframe1, collapse = " VARCHAR, "), " VARCHAR)") — David Arenburg
– David Arenburg, Commented Sep 30, 2014 at 13:30

A5C1D2H2I1M1N2O1R2T1 · Accepted Answer · 2014-10-02 10:50:28Z

2

You can just use a combination of paste and paste0, as suggested by David Arenburg in the comments:

paste0("CREATE TABLE (", 
       paste(dataframe1, collapse = " VARCHAR, "), 
       " VARCHAR)")
# [1] "CREATE TABLE (a VARCHAR, b VARCHAR, c VARCHAR)"

Collectives™ on Stack Overflow

Creating a SQL structure table in R using contents of a dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related