How do I access nested SQL tables in R?

From R Studio's ODBC database documentation I can see a simple example of how to read a SQL table into an R data frame:

data <- dbReadTable(con, "flights")

Let me paste a graphic of the BGBUref table(?) I'm trying to read to an R data frame. This is from my connection pane in R studio.

If I use the same syntax as above, where con is the output of my dbConnect(...) I get the following:

df <- dbReadTable(con, "BGBURef")
#> Error: <SQL> 'SELECT * FROM "BGBURef"' nanodbc/nanodbc.cpp:1587: 42S02: 
#> [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name
#> 'BGBURef'.

Is my understanding of what a "table" is incorrect? Or do I need to do something like this to get to the nested BGBUref table:

df <- dbReadTable(con, "QnRStore\dbo\BGBURef")
#> Error: '\d' is an unrecognized escape in character string starting ""QnRStore\d"

The BGBUref data frame will come up in R Studio if I click on the little spreadsheet icon. I just can't figure out how to get it into a defined data frame, in my case df.

Here's the output when I run these commands:

df <- dbReadTable(con, "QnRStore")
#> Error: <SQL> 'SELECT * FROM "QnRStore"'
#> nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC Driver 17 for SQL 
#> Server][SQL Server]Invalid object name 'QnRStore'.

and:

dbListTables(con)
#>  [1] "spt_fallback_db"                                         
#>  [2] "spt_fallback_dev"                                        
#>  [3] "spt_fallback_usg"                                        
#>  [4] "spt_monitor"                                             
#>  [5] "trace_xe_action_map"                                     
#>  [6] "trace_xe_event_map"                                      
#>  [7] "spt_values"                                              
#>  [8] "CHECK_CONSTRAINTS"                                       
#>  [9] "COLUMN_DOMAIN_USAGE"                                     
#> [10] "COLUMN_PRIVILEGES" 
#> ...
#> [650] "xml_schema_types"                                        
#> [651] "xml_schema_wildcard_namespaces"                          
#> [652] "xml_schema_wildcards"

Solution

General Background

Before anything, consider reading on the relational database architecture where tables are encapsulated in schemas which themselves are encapsulated in databases which are then encapsulated in servers or clusters. Notice the icons in your image correspond to the object type:

cluster/server < catalog/database < schema/namespace < table

Hence, there is no nested tables in your situation but a typical architecture:

myserver < QnRStore < dbo < BGBURef

To access this architecture from server-level in an SQL query, you would use period-qualifying names:

SELECT * FROM database.schema.table

SELECT * FROM QnRStore.dbo.BGBURef

The default schema for SQL Server is dbo (by comparison for Postgres it is public). Usually, DB-APIs like R's odbc connects to a database which allows connection to any underlying schemas and corresponding tables, assuming the connected user has access to such schemas. Please note this rule is not generalizable. For example, Oracle's schema aligns to owner and MySQL's database is synonymous to schema.

See further reading:

Specific Case

Therefore, to connect to an SQL Server database table in a default schema, simply reference the table, BGBURef, which assumes the table resides in the dbo schema of your connecting database.

df <- dbReadTable(con, "BGBURef")

If you use a non-default schema, you will need to specify it accordingly which recently you can do with DBI::Id and can use it similarly for dbReadTable and dbWriteTable:

s <- Id(schema = "myschema", table = "mytable")

df <- dbReadTable(con, s)

dbWriteTable(conn, s, mydataframe)

Alternatively, you can run the needed period qualifying SQL query:

df <- dbGetQuery(con, "SELECT * FROM [myschema].[mytable]")

And you can use SQL() for writing to persistent tables:

dbWriteTable(con, SQL("myschema.mytable"), mydataframe)