SQL server 数据同步 Merge 的一个小bug
a.Name NVarchar(40)
b.Name Varchar(40)
MERGE a AS TARGET
USING (
SELECT
id,name FROM b
) AS SOURCE ON (TARGET.[id] = SOURCE.[id])
WHEN MATCHED AND CHECKSUM(TARGET.Name)!= CHECKSUM(CONVERT(NVARCHAR(40),SOURCE.[Name])) THEN
UPDATE SET TARGET.[Name] = SOURCE.[Name],
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[id]
,[Name]
) VALUES(
SOURCE.[ID]
,SOURCE.[Name]
)
WHEN NOT MATCHED BY SOURCE THEN
Delete ....
USING (
SELECT
id,name FROM b
) AS SOURCE ON (TARGET.[id] = SOURCE.[id])
WHEN MATCHED AND CHECKSUM(TARGET.Name)!= CHECKSUM(CONVERT(NVARCHAR(40),SOURCE.[Name])) THEN
UPDATE SET TARGET.[Name] = SOURCE.[Name],
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[id]
,[Name]
) VALUES(
SOURCE.[ID]
,SOURCE.[Name]
)
WHEN NOT MATCHED BY SOURCE THEN
Delete ....
当b.name 以“-” 结束，如 “test-” 这种情况 ,merge 不起作用。其他情况都还好。
需要把
CHECKSUM(TARGET.Name)!= CHECKSUM(CONVERT(NVARCHAR(40),SOURCE.[Name]))
变成
CHECKSUM(CONVERT(NVARCHAR(40),TARGET.Name）)!= CHECKSUM(SOURCE.[Name])
后才可以同步。
看起来好像是varchar nvarchar转换的问题。
