4

Is there a way to use a data.frame in JOIN condition using sqlQuery()?

I'm connecting to SQL server using RODBC and need to limit the inital result set against a data.frame I've already got in R so it only returns 4000 records out of 200,000. Something like....

My_Data<- as.data.frame(c(1,2,3,4,5,6,7,8))

my_Query<- paste("SELECT * FROM foo INNER JOIN ",My_Data,"ON foo.x  = My_Data.x", sep="")

my_Answer<- sqlQuery(Connection, my_Query)

I can do it by pulling the entire table into R and then removing the data I don't need, but there's got to be a way to do it. I've attempted it one at a time in a FOR loop but it takes longer than pulling the whole table.

My_Data<- as.data.frame(c(1,2,3,4,5,6,7,8))

my_DF <- data.frame()
for(i in 1:length(my_DF)){
  a<- paste(my_Query,my_DF[i])
  b<- sqlQuery(Connection,a)
  my_DF<- rbind(my_DF, b)
}

print(my_DF)
Mattia Maestrini
  • 32,270
  • 15
  • 87
  • 94
Grant
  • 41
  • 3

1 Answers1

2

To reference R data in a SQL query over RODBC, your options are:

  1. Read the SQL data into R then do your manipulations there
  2. Use paste or a similar command to print relevant contents of your R dataframe into your SQL query
  3. Pass the relevant part of your R dataframe into SQL first in a temporary or permanent table and then use SQL do join on it

In the case of #2 join would not be the appropriate SQL operation however, since you're passing a string instead of a table. Try using where, in, and clauses like that which will work on a string of names in your query as opposed to a table.

Hack-R
  • 22,422
  • 14
  • 75
  • 131