Help with extracting multiple SQL queries from the same database using ODBC

[ksingh19]

Hi All,

I am using SQL queries to extract data from main data source (SQL Server). I then transform it using Tidyverse. I am facing an issue where my main database is very long and cannot be uploaded for all the elements that I need. Thus, I am creating subsets from same dabase and storing as different dataframes. Now, the problem is I have several of these subsets and I was hoping there is a way to loop through the same database where I can just change the filters.

Below is what I am currently doing:

library(tidyverse)
library(odbc)
library(DBI)

# Connecting to SQL Server
con <- dbconnect(
odbc(),
driver = "ODBC Driver",
server = "ABCServer",
database = "ABCDatabase",
trusted_connection = "yes")

# Different Dataframes from same database
# Datfaframe1
SQL_query_Table1 <- dbGetQuery(con, "
SELECT  Var1, var2, ...Var10,
       SUM([Var11]) 
FROM [ABCDatabase].[DBO].[Table1]
WHERE Var1 = 'xyz'
AND Date > '2018'
GROUP BY ar1, var2, ...Var10
")

# Datfaframe2
SQL_query_Table2 <- dbGetQuery(con, "
SELECT  Var1, var2, ...Var10,
       SUM([Var11]) 
FROM [ABCDatabase].[DBO].[Table2]
WHERE Var1 = 'abc'
AND Date > '2018'
GROUP BY ar1, var2, ...Var10
")

# Datfaframe3
SQL_query_Table3 <- dbGetQuery(con, "
SELECT  Var1, var2, ...Var10,
       SUM([Var11]) 
FROM [ABCDatabase].[DBO].[Table3]
WHERE Var1 = '123'
AND Date > '2018'
GROUP BY ar1, var2, ...Var10
")

# And several more dataframes
# Disconnect ODBC
dbDisconnect(con)

# Storing  in different variables
Tabel1 <- SQL_query_Table1
Tabel2 <- SQL_query_Table2
Tabel3 <- SQL_query_Table3

# and more tables

Now, I am hoping to avoid this repeatitive task and looping through the same database where filters can be modified. Also, the new Tables1, 2, 3...and more can be stored using correspong SQL Tables.

Any help with the code showing looping or any other way to get this code then more efficiently will be helpful.

Thanks for your help!

[technocrat]

Can you get this function to work?

make_query <- function(x,y,z) {
  # add test that con is open
  SQL_query_Table2 <- dbGetQuery(con, "
  SELECT  Var1, var2, ...Var10,
         SUM([Var11]) 
  FROM [ABCDatabase].[DBO].[y]
  WHERE Var1 = z
  AND Date > '2018'
  GROUP BY ar1, var2, ...Var10
  ")
}