Site icon R-bloggers

SQL Server Schemas & R Tip

[This article was first published on R on Thomas Roh, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

I ran into an issue the other day where I was tring to write a new table to a SQL Server Database with a non-default schema. I did end up spending a bit of time debugging and researching so I wanted to share for anyone else that runs into the issue. Using the DBI::Id function, allows you to specify the schema when you are trying to write a table to a SQL Server database.

DBI::dbWriteTable(con, 
                  DBI::Id(schema = "schema", table = "tablename"), 
                  df)

But the code above will return a strange error:

After some investigation I found a workaround to be able to write the table. For non-default schemas, a “_” needs to in the table name for it to work.

DBI::dbWriteTable(con, 
                  DBI::Id(schema = "schema", table = "tablename_"), 
                  df)

This really isn’t ideal for naming conventions so using the t-sql command sp_rename will rename the table to what I originally wanted.

DBI::dbWriteTable(con, 
                  DBI::Id(schema = "schema", table = "tablename"), 
                  df)
DBI::dbGetQuery(con, "USE database;
EXEC sp_rename '[schema].[tablename_]', 'tablename';")

I ran into the same issues for overwriting tables as well but a workflow for doing the same is simply to use sp_rename a couple of times.

DBI::dbGetQuery(con, "USE database;
EXEC sp_rename '[schema].[tablename]', 'tablename_';")
DBI::dbWriteTable(con, 
                  DBI::Id(schema = "schema", table = "tablename_"), 
                  df, 
                  overwrite = TRUE)
DBI::dbGetQuery(con, "USE database;
EXEC sp_rename '[schema].[tablename_]', 'tablename';")

To leave a comment for the author, please follow the link and comment on their blog: R on Thomas Roh.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.