Search code examples
rprintfrodbcsqldf

Bringing a R dataframe into a sql usable list with paste function

I have a dataframe/list in R(Rdataframe) which I want to use directly in RODBC query something like 

Rdataframe= c('123456','234561','678912')
a= sqlQuery(connection, "Select * from table A where A.Id in Rdataframe")

And the query has to be like that, i.e. I can't pull the table first in R and then do the look up

So I think it can only run if it will come in the format such as 

a= sqlQuery(connection, "Select * from table A where A.Id in ('123456','234561','678912'))

But despite of several attempts of sprintf & paste I am still not successful.

This is what I tried to attempt but failed 

attempt1= sqlQuery(connection, sprintf("Select * from table A where A.Id in %s", Rdataframe))

attempt2=paste(Rdataframe, sep=",")

And then using this attempt 2 structure in query.

Every help is important

Solution 
  • Rdataframe= c('123456' , '234561' , '678912')
df_str = paste(Rdataframe , collapse = "','" , sep=" ")
queryStr = paste("Select * from table A where A.Id in ('" ,df_str , "')" , sep="")
print(queryStr)

    Gives the output

    [1] "Select * from table A where A.Id in ('123456','234561','678912')"