sql-server - 如何使用 DBI 从 R 写入具有特定模式的 SQL Server 表？

Question

我在使用 dbWriteTable 将我的数据帧输出到 SQL Server 表时遇到问题。我可以在模式为默认模式时执行此操作，但如果我尝试使用备用模式则不行。见下文:

# Before running this example, create the table in SQL Server via
# CREATE TABLE [guest].[MikeTestTable](
# [a] [float] NULL,
# [b] [float] NULL,
# [c] [varchar](255) NULL)

# df to insert
df <- data.frame(a=c(10,20,30),
                 b=c(20,40,60),
                 c=c('oneT','twoT','threeT'))

# connection string
MSSQLConnectionString <- '
  driver={SQL Server};
  server=localhost;
  database=SAM;
  trustedConnection=true;
  '

# DBI connection
con <- DBI::dbConnect(odbc::odbc(),
                      .connection_string = MSSQLConnectionString)

# query to read from table
q <- "SELECT [a]
,[b]
,[c]
FROM HCRWriteData"

# get data
dfOut <- DBI::dbGetQuery(con, q)

head(dfOut)
 > [1] a b c
 > <0 rows> (or 0-length row.names)

# now try to write contents of df to the table.
# this table uses the default schema.
DBI::dbWriteTable(conn = con, 
                  name = 'HCRWriteData', 
                  value = df,
                  append = TRUE)

# get data after writing
dfOut <- DBI::dbGetQuery(con, q)
head(dfOut
     > a  b      c
     > 1 10 20   oneT
     > 2 20 40   twoT
     > 3 30 60 threeT

### Using non-default schema
# query to read from table
q2 <- "SELECT [a]
,[b]
,[c]
FROM [SAM].[guest].[MikeTestTable]"

# get data
dfOut <- DBI::dbGetQuery(con, q2)
head(dfOut)
     > [1] a b c
     > <0 rows> (or 0-length row.names)

#Finally, writing to the non-default schema:
# this table uses a non-default schema
DBI::dbWriteTable(conn = con, 
                  name = 'guest.MikeTestTable', 
                  value = df,
                  append = TRUE)

# get data
dfOut <- DBI::dbGetQuery(con, q2)
head(dfOut)
     >  Error: Table guest.MikeTestTable does not exist

我可以从任一表读取，但只能写入默认架构。

Answer 1

尝试像这样使用 SQL:

dbWriteTable(con, SQL('guest.MikeTestTable'), df, append = TRUE)

参见最后一个示例 here