INSERT...ON重复键更新是否比删除/插入所有记录更快

Question

我正在尝试同步两组表，每组表在结构上(大致)相同，一组在Microsoft SQL Server数据库上，另一组在MySQL数据库上，MSSQL有一个链接到MySQL数据库的服务器，称为LNK-SVR

我想知道哪个更快，是使用INSERT INTO ... SELECT FROM查询删除一个表中的每条记录并重新插入，还是使用INSERT...ON DUPLICATE KEY UPDATE创建任何新记录并更新任何主键冲突

Answer 1

在Sql Server 2008或更早版本中，您应该使用Merge语句来同步两个表：

看看下面的示例:运行后，两个表(@sourceTableand和@targetTable)都有相同的行。

DECLARE @sourceTable TABLE(id INT PRIMARY KEY, somefield VARCHAR(255))

DECLARE @targetTable TABLE(id INT PRIMARY KEY, somefield VARCHAR(255))

INSERT INTO @sourceTable (id, somefield) VALUES(1, 'SourceValue1'),
 (3, 'SourceValue3')

INSERT INTO @targetTable (id, somefield) VALUES(1,
 'TargetValue1'), (2, 'TargetValue2')

SELECT * FROM @sourceTable SELECT * FROM @targetTable

MERGE @targetTable AS target USING @sourceTable AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET somefield = source.somefield
WHEN NOT MATCHED THEN   INSERT (id, somefield) VALUES(id, source.somefield)
WHEN NOT MATCHED BY source THEN DELETE;

SELECT * FROM @sourceTable
SELECT * FROM @targetTable