R is a popular statistical programming language that provides several packages to connect to databases and fetch data from them. SQL Server is a widely used relational database management system, and we can fetch data from it in R using the ODBC connection. In this tutorial, we will learn how to copy data from SQL Server to R using the ODBC connection.

ODBC (Open Database Connectivity) is a standard application programming interface (API) for accessing and manipulating databases. ODBC allows R to connect to various database systems like SQL Server, Oracle, MySQL, etc. SQL Server is a relational database management system that is widely used in organizations.

Explore Free Engineering Handwritten Notes!

Looking for comprehensive study materials on Python, Data Structures and Algorithms (DSA), Object-Oriented Programming (OOPs), Java, Software Testing, and more?

Download Now

We earn a commission if you make a purchase, at no additional cost to you.

Copy data from SQL Server to R using ODBC connection

To copy data from SQL Server to R using the ODBC connection, follow these steps:

Step 1: Install and load the RODBC package in R.

install.packages("RODBC")
library(RODBC)

Step 2: Create a connection object using the odbcConnect() function in R.

con <- odbcConnect("my_database_name", uid = "my_username", pwd = "my_password")

Here, replace my_database_name, my_username, and my_password with your database name, username, and password, respectively.

Step 3: Execute a SQL query using the sqlQuery() function in R.

data <- sqlQuery(con, "SELECT * FROM my_table_name")

Here, replace my_table_name with the name of the table you want to fetch data from.

Step 4: Close the connection using the odbcClose() function in R.

odbcClose(con)

Example

# Install and load the RODBC package
install.packages("RODBC")
library(RODBC)

# Create a connection object
con <- odbcConnect("my_database_name", uid = "my_username", pwd = "my_password")

# Execute a SQL query
data <- sqlQuery(con, "SELECT * FROM my_table_name")

# Close the connection
odbcClose(con)

# View the fetched data
head(data)

Output screenshot: Here is the output screenshot for fetching data from a SQL Server table in R