如何逐行检查两个表?如果具有旧记录的秒表应该更新? [英] how to check two tables by row by row ?if secound table having old record it should be updated ?

问题描述

目的地表应该被更新..

destintion table should be updated..

推荐答案

使用 BINARY_CHECKSUM

假设您在两个表上都有PrimaryKey Column(或任何Unique列)，例如说Id

Use BINARY_CHECKSUM

Suppose you have PrimaryKey Column(or any Unique column) on two tables, let''s say Id

SELECT T1.*
FROM
(
SELECT *, CAST(BINARY_CHECKSUM(*) AS BIGINT)  AS BCHECKSUM
FROM Table1
--WHERE Id IN(.,.,..) -- To be used in subsequent iteration
)AS T1
LEFT OUTER JOIN
(
SELECT Id, CAST(BINARY_CHECKSUM(*) AS BIGINT) AS BCHECKSUM
FROM Table2
--WHERE Id IN(.,.,..) -- To be used in subsequent iteration
)AS T2
ON T1.Id = T2.Id
WHERE T1.BCHECKSUM <> T2.BCHECKSUM

现在您有不匹配的行...记下不匹配的ID，并继续使用BINARY_CHECKSUM(Col1，Col2，...，Coln)(即最初我们使用*，现在我们选择特定的列来缩小对特定列的不匹配的范围行(但仅限于不匹配的ID)，并继续逐步删除每一列，直到看到BINARY_CHECKSUM相同为止.

Now you have unmatched rows... Note down Ids for unmatched and continue with BINARY_CHECKSUM(Col1, Col2, ..., Coln) (i.e. initially we used *, now we select specific columns to narrow down unmatch to specific column''s row) on the above(but it restrict to the Ids that are not matching) script and keep on removing each column in step until you see BINARY_CHECKSUM is same.

这篇关于如何逐行检查两个表?如果具有旧记录的秒表应该更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！