如何用T-SQL模拟SSMS导入导出实现远程表快速同步？

原存储过程执行缓慢的核心原因是：DELETE 会逐行记录日志且触发约束/触发器，跨服务器的逐行INSERT也会产生大量网络开销和日志写入。SSMS导入导出工具本质是用批量操作+最小化日志的逻辑，以下是对应的T-SQL实现方案：

一、快速清空远程表（替代DELETE） #

用TRUNCATE TABLE替代DELETE，它属于DDL操作，直接释放数据页，日志量极小，速度远快于DELETE。注意：需要ALTER TABLE权限，且若表存在外键约束，需先禁用外键。

-- 先禁用远程库所有表的外键约束（若有）
EXEC [LinkedServer].[DB1].[sys].[sp_msforeachtable] 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- 快速清空表
TRUNCATE TABLE [LinkedServer].[DB1].[dbo].[Table1]
TRUNCATE TABLE [LinkedServer].[DB1].[dbo].[Table2]
TRUNCATE TABLE [LinkedServer].[DB1].[dbo].[Table3]

二、批量插入优化（模拟导入导出的批量逻辑） #

SSMS导入导出默认使用批量插入模式，T-SQL中可以通过WITH (TABLOCK)提示启用批量日志优化，同时禁用触发器进一步提速：

-- 禁用远程表所有触发器
EXEC [LinkedServer].[DB1].[sys].[sp_msforeachtable] 'DISABLE TRIGGER ALL ON ?'

-- 批量插入，TABLOCK让SQL Server采用批量日志模式，大幅减少日志开销
INSERT INTO [LinkedServer].[DB1].[dbo].[Table3] WITH (TABLOCK)
SELECT * FROM [DB1].[dbo].[Table3]

INSERT INTO [LinkedServer].[DB1].[dbo].[Table2] WITH (TABLOCK)
SELECT * FROM [DB1].[dbo].[Table2]

INSERT INTO [LinkedServer].[DB1].[dbo].[Table1] WITH (TABLOCK)
SELECT * FROM [DB1].[dbo].[Table1]

三、恢复约束与触发器 #

操作完成后恢复原有的约束和触发器：

-- 启用外键约束
EXEC [LinkedServer].[DB1].[sys].[sp_msforeachtable] 'ALTER TABLE ? CHECK CONSTRAINT ALL'

-- 启用触发器
EXEC [LinkedServer].[DB1].[sys].[sp_msforeachtable] 'ENABLE TRIGGER ALL ON ?'

四、关于BULK INSERT的表间传输用法 #

BULK INSERT本身仅支持从文件导入，但可以结合BCP和远程执行实现类似效果，适合超大规模数据（步骤略繁琐）：

1. 用BCP将本地表导出到共享文件：

EXEC xp_cmdshell 'bcp DB1.dbo.Table1 out "\\YourServer\SharedFolder\Table1.dat" -S LocalDBInstance -T -n'

2. 在远程服务器执行BULK INSERT导入文件：

EXEC [LinkedServer].[DB1].[dbo].[sp_executesql] N'
BULK INSERT [dbo].[Table1]
FROM ''\\YourServer\SharedFolder\Table1.dat''
WITH (
    TABLOCK,
    FORMATFILE = ''\\YourServer\SharedFolder\Table1.fmt'' -- 需提前用BCP生成格式文件
)'

额外注意事项 #

• 若远程表有自增列，插入前需执行SET IDENTITY_INSERT [LinkedServer].[DB1].[dbo].[TableX] ON，插入后再关闭
• 确保本地与远程表的字段顺序、数据类型完全匹配，避免SELECT *引发的结构不兼容问题
• 若远程服务器开启了严格的事务日志模式，可临时切换为批量日志模式进一步优化速度

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