如何用SELECT NOT EXISTS跨服务器向SQL表插入新增记录？

问题背景 #

• 需求：监控STLEDGSQL01.MES_DEV.dbo.wrap_labor（ServerB的TableB）的ID列，将MACOLA_TABLES_SQL01.dbo.wrap_labor_SSIS（ServerA的TableA）中不存在的新增记录插入至TableA。
• 已完成操作：创建SSIS包并计划通过SQL Server Agent每小时调度执行；已建立链接服务器，可查询出待插入的新增记录，但无法完成批量插入。

可用的待插入数据查询语句 #

SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM STLEDGSQL01.MES_DEV.dbo.wrap_labor AS SRC
WHERE (NOT EXISTS (SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM wrap_labor_SSIS AS TGT WHERE (TGT.ID = SRC.ID)))

尝试过的失败插入语句 #

INSERT INTO [MACOLA_TABLES_SQL01].[dbo].[wrap_labor_SSIS] VALUES (
SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM STLEDGSQL01.MES_DEV.dbo.wrap_labor AS SRC
WHERE (NOT EXISTS (SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM wrap_labor_SSIS AS TGT WHERE (TGT.ID = SRC.ID))))

解决方案 #

正确的批量插入SQL语句 #

你的插入语句错误使用了VALUES()语法，INSERT...SELECT模式不需要VALUES关键字，直接将查询语句衔接在INSERT INTO之后即可：

INSERT INTO [MACOLA_TABLES_SQL01].[dbo].[wrap_labor_SSIS]
(
    ID, trx_date, work_order, Department, work_center, operation_no, 
    operator, total_labor_hrs, job_start, job_end, qty_ordered, 
    qty_produced, item_no, lot_no, default_bin, posted, wrapped, 
    total_shift_hrs, check_emp, machine, operation_complete
)
SELECT 
    ID, trx_date, work_order, Department, work_center, operation_no, 
    operator, total_labor_hrs, job_start, job_end, qty_ordered, 
    qty_produced, item_no, lot_no, default_bin, posted, wrapped, 
    total_shift_hrs, check_emp, machine, operation_complete
FROM STLEDGSQL01.MES_DEV.dbo.wrap_labor AS SRC
WHERE NOT EXISTS 
(
    SELECT 1 
    FROM wrap_labor_SSIS AS TGT 
    WHERE TGT.ID = SRC.ID
)

优化说明 #

1. 简化子查询：NOT EXISTS子查询中不需要返回所有列，只需要用SELECT 1判断ID是否存在即可，能大幅提升查询效率。
2. 显式指定列：插入时显式列出目标表的列，避免因表结构变化导致的插入错误，同时提升语句可读性。

可选方案：使用MERGE语句（适合复杂同步场景） #

如果后续需要支持更新或更复杂的同步逻辑，可以使用MERGE语句：

MERGE [MACOLA_TABLES_SQL01].[dbo].[wrap_labor_SSIS] AS TGT
USING STLEDGSQL01.MES_DEV.dbo.wrap_labor AS SRC
ON TGT.ID = SRC.ID
WHEN NOT MATCHED THEN
INSERT 
(
    ID, trx_date, work_order, Department, work_center, operation_no, 
    operator, total_labor_hrs, job_start, job_end, qty_ordered, 
    qty_produced, item_no, lot_no, default_bin, posted, wrapped, 
    total_shift_hrs, check_emp, machine, operation_complete
)
VALUES 
(
    SRC.ID, SRC.trx_date, SRC.work_order, SRC.Department, SRC.work_center, SRC.operation_no, 
    SRC.operator, SRC.total_labor_hrs, SRC.job_start, SRC.job_end, SRC.qty_ordered, 
    SRC.qty_produced, SRC.item_no, SRC.lot_no, SRC.default_bin, SRC.posted, SRC.wrapped, 
    SRC.total_shift_hrs, SRC.check_emp, SRC.machine, SRC.operation_complete
);

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