Load R data frames into an MS SQL Server database and modify MS SQL Server tables with R. For help documentation, see the package website.
Installation
If you are working within the Scottish Government, the package can be installed in the same way as other R packages internally.
Alternatively, the package can be installed directly from Github or locally from zip.
To install directly from GitHub:
remotes::install_github("DataScienceScotland/rtosqlserver", upgrade = "never")If the above does not work, install from manual download:
- Download the zip of the repository from GitHub.
- Save the downloaded zip to a specific directory (e.g. C:/temp).
- Install with this command specifying the path to the downloaded zip:
remotes::install_local("C:/temp/RtoSQLServer-main.zip", upgrade="never")Functionality
As well as loading R dataframes into SQL Server databases, functions are currently available to:
- Read a database table into an R dataframe, optionally specifying a subset of table columns or row filter.
- Rename and drop a table from the database.
- Add, drop, rename columns from existing database tables.
- Read existing table metadata (columns, data types, summary info).
- List all tables and views in a schema.
- Create an MS SQL Server database connection object for use with DBI or dbplyr packages.
- Run any other input sql in the database and return a data frame if a select statement.
Example
See Get started for examples of the main functionality.
# Loading data frame example
library(RtoSQLServer)
# Make a test dataframe with n rows
test_n_rows <- 123456
test_df <- data.frame(a = rep("a", test_n_rows), b = rep("b", test_n_rows))
# Set database connection details:
server <- "server\\instance"
database <- "my_database_name"
schema <- "my_schema_name"
# Write the test dataframe to a SQL Server table
write_dataframe_to_db(
server = server,
database = database,
schema = schema,
table_name = "test_r_tbl",
dataframe = test_df
)