Executing SQL from R

Question

I would like to execute SQL query from R and get results into dataframe. Here is my example.

library(RODBC)
db.handle <-odbcDriverConnect('driver=
{SQL Server Native Client 11.0};server=some_server\\some_server_2;
database = some_db;trusted_connection=yes')

query <-"select top 10 * into #temp_table from table_A select * from #temp_table"

res <- sqlQuery(db.handle, query)

print(res)

The above code return character(0). It works without #temp_table. Is there way to make it work with temp table?

Answer 1

While this may be a valid SQL statement, I'm not sure the the 'RODBC' package understands what to do with your query with the sqlQuery() command alone.

Assuming you have read and write permissions to the database you're using, try separating the functional operation into parts: sqlQuery(), sqlSave(), sqlQuery(). If you need to remove an existing temp table, you can use sqlDrop(). Don't forget to close the connection when finished.

Alternatively, you could run SQL like transformations in R with 'dplyr' after getting results from the 'select top 10 from table_A' query.