跨不同系统数据库查询问题：合并Management_Reporting与MPS数据

1. 解决UNION ALL字段不匹配问题 #

UNION ALL要求两个查询返回的字段数量一致，且对应位置的字段类型兼容。由于两张表的业务字段差异大，需要为缺失的字段补NULL值，并统一字段名和类型：

示例SQL（同实例跨库场景） #

-- 从Management_Reporting提取数据，补全血液检测字段为NULL
SELECT 
    PatientID,
    DrugRegime,
    CAST(NULL AS DECIMAL(10,2)) AS FSH, -- 类型匹配BloodTest的FSH字段
    CAST(NULL AS DECIMAL(10,2)) AS E2,
    CAST(NULL AS DECIMAL(10,2)) AS P4,
    'TreatmentSummary' AS SourceTable -- 可选：标记数据来源表
FROM Management_Reporting.dbo.TreatmentSummary

UNION ALL

-- 从MPS提取数据，补全DrugRegime字段为NULL
SELECT 
    PatientId AS PatientID, -- 统一字段名（消除大小写差异）
    CAST(NULL AS VARCHAR(100)) AS DrugRegime, -- 类型匹配TreatmentSummary的DrugRegime字段
    FSH,
    E2,
    P4,
    'BloodTest' AS SourceTable -- 可选：标记数据来源表
FROM MPS.dbo.BloodTest

注意：CAST(NULL AS 类型)中的类型必须和对应表的字段类型完全一致（比如DrugRegime是VARCHAR(50)就用VARCHAR(50)，FSH是FLOAT就用FLOAT），避免类型不兼容错误。

2. 跨不同数据库实例的查询配置 #

如果两个数据库属于不同SQL Server实例，需要先创建「链接服务器」来建立跨实例访问：

方法1：通过SSMS图形界面创建 #

• 打开SSMS，展开「服务器对象」→「链接服务器」→ 右键选择「新建链接服务器」
• 「常规」选项卡：输入远程实例名称，服务器类型选择「SQL Server」
• 「安全性」选项卡：根据认证方式选择：
  • Windows认证：选择「使用登录名的当前安全上下文建立连接」（需本地账号在远程实例有访问权限）
  • SQL认证：选择「使用此安全上下文建立连接」，输入远程实例的用户名和密码

方法2：用T-SQL创建链接服务器 #

-- 创建链接服务器，自定义名称为MPS_Server
EXEC sp_addlinkedserver 
    @server = N'MPS_Server',
    @srvproduct=N'SQL Server';

-- 配置SQL认证的登录映射（Windows认证可跳过此步）
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname=N'MPS_Server',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'远程SQL账号',
    @rmtpassword=N'远程账号密码';

跨实例查询示例 #

创建链接服务器后，使用[链接服务器名].[数据库名].[架构名].[表名]的格式访问远程表，结合UNION ALL的完整SQL如下：

SELECT 
    PatientID,
    DrugRegime,
    CAST(NULL AS DECIMAL(10,2)) AS FSH,
    CAST(NULL AS DECIMAL(10,2)) AS E2,
    CAST(NULL AS DECIMAL(10,2)) AS P4,
    'TreatmentSummary' AS SourceTable
FROM Management_Reporting.dbo.TreatmentSummary

UNION ALL

SELECT 
    PatientId AS PatientID,
    CAST(NULL AS VARCHAR(100)) AS DrugRegime,
    FSH,
    E2,
    P4,
    'BloodTest' AS SourceTable
FROM MPS_Server.MPS.dbo.BloodTest

注意事项 #

• 确保两个数据库实例之间网络连通，默认SQL Server端口1433已开放
• 登录账号对两个数据库的目标表拥有SELECT权限
• 若PatientID/PatientId类型不一致（如一个是INT一个是VARCHAR），需用CAST统一类型，例如CAST(PatientId AS INT) AS PatientID

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