1 Set up the connection
2 Build your query
3 Execute your query
4 Close your connection
5 Do stuff to your data

Here’s a quick guide on connecting to SQL Server from R using the RODBC package. In saying that, make sure you have RODBC package installed by going:

require(RODBC)

## Loading required package: RODBC

If you don’t see the loading message above, install the package from CRAN:

install.packages("RODBC")

1 Set up the connection

Assuming the machine you’re on already has a System Data Source configured within the ODBC Data Source Administrator. This should be the case if the SQL Server uses Windows Authentication.

con <- odbcConnect("DSNName")

If you use a username - password combination to log in, do this instead:

con <- odbcConnect("DatabaseName", uid = "user", pwd = "pass")

2 Build your query

Insert your query into a string. Note that you can do more than just the SELECT keyword.

query <- "SELECT * FROM db.schema.tablename"

3 Execute your query

Execute your query with the sqlQuery function. You can save the results to an object so you can do stuff with it. If you don’t the results will just be printed in the console for you.

# query printed to console
sqlQuery(con, query)
# query saved to data object
data <- sqlQuery(con, query)

4 Close your connection

Once you’ve retrieved the data you need, it is good practice to close the connection.

close(con)

5 Do stuff to your data

Congratulations, you can now do stuff to your data :)