如何在R中用本地data.table与数据库表实现内连接查询？

你代码里的#param是SQL Server的会话级临时表，但你的param是R本地的data.table，数据库服务器根本无法识别这个本地对象，所以会抛出“object param is not found”的错误。

以下两种方法可以实现本地data.table与数据库表的内连接：

方法1：将本地表上传到数据库临时表后执行SQL查询 #

先把R本地的param写入数据库的临时表#param，再执行原查询，数据库就能识别临时表了：

library(DBI)
library(data.table)

# 1. 将本地data.table写入数据库临时表（会话结束后自动销毁）
dbWriteTable(
  conn = conCompass,
  name = "#param",
  value = param,
  temporary = TRUE,  # 创建临时表
  overwrite = TRUE    # 若临时表已存在则覆盖
)

# 2. 执行连接查询（推荐用glue_sql做参数化，避免SQL注入和格式错误）
library(glue)
query <- glue_sql(
  "select 
     case when m.chgeffdt > getdate() then 'F' else 'C' end, 
     m.CLNTCODE, m.POLNO, CERTNO, 
     m.clntcode + 'Cert' + certno + 'Dep01', 
     PRODCODE, BENPLNCD, COVGCODE, m.INIEFFDT, m.CHGEFFDT, 
     m.RCDUSRID, getdate(), PRDSTATUS, 
     isnull(PROLFIA,0), isnull(INFLFIA,0), isnull(NELAMT,0), 
     m.CHGEFFDATE, 
     m.polno+certno+prodcode+benplncd+covgcode+convert(char(8),m.chgeffdt,112)+'C' 
   from #param p (nolock) 
   inner join TMEMPTpOL m with (nolock) 
     on p.clntcode=m.clntcode 
     and p.rcdsts='A'
     and m.INIEFFDT<= {EffectiveDate} 
     and m.chgeffdt<= {AsOfDate}",
  .con = conCompass  # 指定数据库连接，自动处理参数格式
)

TMEMPTPOL <- dbGetQuery(conCompass, query) %>% as.data.table()

方法2：用dbplyr实现R风格的跨环境连接 #

借助dbplyr可以直接用R语法编写查询，它会自动把本地表上传到数据库临时表并生成对应SQL，无需手动写原生SQL：

library(dplyr)
library(dbplyr)
library(data.table)

# 将数据库表转为远程查询对象
tmemptpol_remote <- tbl(conCompass, "TMEMPTpOL")

# 执行连接与数据处理，dbplyr自动处理本地表到数据库的上传
TMEMPTPOL <- param %>%
  filter(rcdsts == 'A') %>%
  inner_join(tmemptpol_remote, by = "CLNTCODE") %>%
  filter(INIEFFDT <= !!EffectiveDate, CHGEFFDT <= !!AsOfDate) %>%
  mutate(
    status_flag = case_when(CHGEFFDT > Sys.Date() ~ 'F', TRUE ~ 'C'),
    custom_id = paste0(CLNTCODE, "Cert", CERTNO, "Dep01"),
    composite_key = paste0(POLNO, CERTNO, PRODCODE, BENPLNCD, COVGCODE, format(CHGEFFDT, "%Y%m%d"), "C"),
    current_date = Sys.Date(),
    PROLFIA = ifelse(is.na(PROLFIA), 0, PROLFIA),
    INFLFIA = ifelse(is.na(INFLFIA), 0, INFLFIA),
    NELAMT = ifelse(is.na(NELAMT), 0, NELAMT)
  ) %>%
  select(
    status_flag, CLNTCODE, POLNO, CERTNO, custom_id, PRODCODE, BENPLNCD, COVGCODE,
    INIEFFDT, CHGEFFDT, RCDUSRID, current_date, PRDSTATUS, PROLFIA, INFLFIA,
    NELAMT, CHGEFFDATE, composite_key
  ) %>%
  collect() %>%  # 将查询结果拉回R本地
  as.data.table()  # 转为data.table格式

• 方法1中如果不用glue_sql，直接字符串拼接日期参数时，要确保EffectiveDate和AsOfDate是数据库能识别的格式（如'YYYY-MM-DD'），否则会触发日期格式错误。
• 方法2的!!符号用于将R本地的变量注入到dbplyr查询中，确保参数能正确传递。

内容的提问来源于stack exchange，提问作者Antreas Touloupis