SQL语句在SSMS正常运行但R中返回空结果，临时表是否为诱因？

没错，本地临时表（#mapDT）正是导致空结果的核心原因。SQL Server的本地临时表（以单个#开头）是会话/语句句柄级别的私有对象——当你在R的dbGetQuery中一次性执行多语句批处理时，ODBC驱动可能会将创建临时表、插入数据和查询这几个步骤拆分到不同的内部会话/句柄中，导致执行SELECT时，之前创建的#mapDT已经不存在了，自然返回空结果。

针对R环境的解决方案 #

这里有几个靠谱的解决办法，按推荐程度排序：

1. 用CTE替代临时表（最简洁，无会话问题） #

把临时表的映射逻辑直接写成公共表表达式（CTE），不需要创建临时表，所有逻辑在单个查询中完成：

library(odbc)
conn <- dbConnect(odbc(), Driver = "SQL Server", Server = "MYSERVER\\MYINSTANCE", Database = "MYDATABASE", Trusted_Connection = "True")

query <- "
WITH mapDT AS (
    SELECT 'varchar' AS SqlDatatype, 'type text' AS MyNewDataType UNION ALL
    SELECT 'datetime', 'type datetime' UNION ALL
    SELECT 'tinyint', 'int64.Type' UNION ALL
    SELECT 'int', 'int64.Type' UNION ALL
    SELECT 'float', 'type number'
)
SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN mapDT m on m.SqlDatatype = C.DATA_TYPE 
WHERE TABLE_NAME = 'MYTABLE';
"

result <- dbGetQuery(conn, query)

2. 使用全局临时表（适用于复杂逻辑） #

如果必须用临时表，把本地临时表改成全局临时表（以##开头），它会在整个连接生命周期内可见：

query <- "
IF OBJECT_ID('tempdb..##mapDT') IS NOT NULL DROP TABLE ##mapDT;
CREATE TABLE ##mapDT (SqlDatatype varchar(64), MyNewDataType varchar(64));
INSERT INTO ##mapDT 
SELECT 'varchar','type text' UNION ALL 
SELECT 'datetime','type datetime' UNION ALL 
SELECT 'tinyint','int64.Type' UNION ALL 
SELECT 'int','int64.Type' UNION ALL 
SELECT 'float','type number';

SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN ##mapDT m on m.SqlDatatype = C.DATA_TYPE 
WHERE TABLE_NAME = 'MYTABLE';

-- 用完记得删除全局临时表，避免影响其他会话
DROP TABLE ##mapDT;
"

result <- dbGetQuery(conn, query)

⚠️ 注意：全局临时表对所有连接可见，所以要确保用完就删除，避免并发冲突。

3. 拆分操作到同一个连接会话 #

通过dbSendQuery和dbFetch手动控制会话，确保所有语句在同一个句柄中执行：

conn <- dbConnect(odbc(), Driver = "SQL Server", Server = "MYSERVER\\MYINSTANCE", Database = "MYDATABASE", Trusted_Connection = "True")

# 创建并填充临时表
dbExecute(conn, "
IF OBJECT_ID('tempdb..#mapDT') IS NOT NULL DROP TABLE #mapDT;
CREATE TABLE #mapDT (SqlDatatype varchar(64), MyNewDataType varchar(64));
INSERT INTO #mapDT 
SELECT 'varchar','type text' UNION ALL 
SELECT 'datetime','type datetime' UNION ALL 
SELECT 'tinyint','int64.Type' UNION ALL 
SELECT 'int','int64.Type' UNION ALL 
SELECT 'float','type number';
")

# 执行查询
result <- dbGetQuery(conn, "
SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN #mapDT m on m.SqlDatatype = C.DATA_TYPE 
WHERE TABLE_NAME = 'MYTABLE';
")

# 清理临时表
dbExecute(conn, "DROP TABLE #mapDT;")

这个方法需要确保dbExecute和dbGetQuery使用的是同一个连接对象，且连接没有被自动回收。

针对Python环境的解决方案 #

如果你用Python（比如pyodbc库），思路和R一致：

1. CTE方案（推荐） #

import pyodbc

conn_str = (
    "DRIVER={SQL Server};"
    "SERVER=MYSERVER\\MYINSTANCE;"
    "DATABASE=MYDATABASE;"
    "Trusted_Connection=yes;"
)
conn = pyodbc.connect(conn_str)

query = """
WITH mapDT AS (
    SELECT 'varchar' AS SqlDatatype, 'type text' AS MyNewDataType UNION ALL
    SELECT 'datetime', 'type datetime' UNION ALL
    SELECT 'tinyint', 'int64.Type' UNION ALL
    SELECT 'int', 'int64.Type' UNION ALL
    SELECT 'float', 'type number'
)
SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN mapDT m on m.SqlDatatype = C.DATA_TYPE 
WHERE TABLE_NAME = 'MYTABLE';
"""

cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()

2. 同一个游标执行所有语句 #

import pyodbc

conn_str = (
    "DRIVER={SQL Server};"
    "SERVER=MYSERVER\\MYINSTANCE;"
    "DATABASE=MYDATABASE;"
    "Trusted_Connection=yes;"
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# 执行临时表创建和插入
cursor.execute("""
IF OBJECT_ID('tempdb..#mapDT') IS NOT NULL DROP TABLE #mapDT;
CREATE TABLE #mapDT (SqlDatatype varchar(64), MyNewDataType varchar(64));
INSERT INTO #mapDT 
SELECT 'varchar','type text' UNION ALL 
SELECT 'datetime','type datetime' UNION ALL 
SELECT 'tinyint','int64.Type' UNION ALL 
SELECT 'int','int64.Type' UNION ALL 
SELECT 'float','type number';
""")

# 执行查询
cursor.execute("""
SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN #mapDT m on m.SqlDatatype = C.DATA_TYPE 
WHERE TABLE_NAME = 'MYTABLE';
""")
result = cursor.fetchall()

# 清理
cursor.execute("DROP TABLE #mapDT;")
conn.commit()

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