验证一个表中的行是否与另一个表中的行匹配

I'm working on an archiving project right now which involves moving data out of tables in one database to tables in an archive database. The process goes as follows:

我正在研究一个归档项目，它涉及将数据从一个数据库中的表移出到归档数据库中的表。过程如下：

Find a record you want to archive
查找要存档的记录
Verify it isn't already archived
确认它尚未存档
Copy the data over
复制数据
Verify all the data is copied over
验证是否复制了所有数据
Delete the data from the source
从源中删除数据

I'm looking for how to do step 4. I want to make sure that every value in a column in a given row in my archive table is exactly the same as every value in a column in a given row in my production table. I just want to do this so I know it will be safe to delete the data out of the production table. The tables that i'm working with both have identical columns. Right now, I'm simply checking to see if the Id of row in production exists in the archive before I delete it. I know I can do a lot better, but I'm not a true expert on SQL. The solution I can think of that falls within my current knowledge would be long and painful, so I'm seeing if there's a quick, one query way of doing this.

我正在寻找如何执行第4步。我想确保归档表中给定行的列中的每个值与生产表中给定行的列中的每个值完全相同。我只想这样做，所以我知道从生产表中删除数据是安全的。我正在使用的表都具有相同的列。现在，我只是在删除之前检查存档中是否存在生产行的Id。我知道我可以做得更好，但我不是真正的SQL专家。我能想到的解决方案属于我目前的知识，这将是漫长而痛苦的，所以我看到是否有一种快速，一种查询方式来做到这一点。

This is pseudocode and I'm mixing my C# with this, but I need something like:

这是伪代码，我将我的C＃与此混合，但我需要这样的东西：

(SELECT * FROM [Production].[dbo].[Table1] WHERE Id = '1234') == (SELECT * FROM [Archive].[dbo].[Table1] WHERE Id = '1234')

And yes, I'm well aware that isn't correct syntax ;)

是的，我很清楚这是不正确的语法;）

2 个解决方案

#1

Try this:

尝试这个：

Sample Data:

样本数据：

DECLARE @Archive AS TABLE (id varchar(1), other varchar(1), another varchar(2), yetanother varchar(2))
DECLARE @Production AS TABLE (id varchar(1), other varchar(1), another varchar(2))

INSERT INTO @Production  VALUES('1','1','11')
INSERT INTO @Production  VALUES('2','2','22')
INSERT INTO @Archive VALUES('1','1','11','11')
INSERT INTO @Archive VALUES('3','3','33','33')

Query:

查询：

DECLARE @id INT = 1

SELECT COUNT(*)
FROM
(SELECT id, other, another FROM @Production WHERE Id = @id
EXCEPT
SELECT id, other, another FROM @Archive WHERE Id = @id) AS x

0 indicates no match!

0表示不匹配！

Using your exact example you would have:

使用您的确切示例，您将拥有：

SELECT COUNT(*)
FROM
(SELECT id, col1, col2, etc... FROM [Production].[dbo].[Table1] WHERE Id = '1234'
EXCEPT
SELECT id, col1, col2, etc... FROM [Archive].[dbo].[Table1] WHERE Id = '1234') AS x

More info on EXCEPT here

有关除此之外的更多信息

#2

If you run the following query you can compare manually;

如果运行以下查询，则可以手动比较;

SELECT * FROM [Production].[dbo].[Table1] pt
FULL OUTER JOIN [Archive].[dbo].[Table1] at ON pt.id=at.id

Depending what fields you have you can do;

根据您所拥有的领域，您可以做;

SELECT * FROM [Production].[dbo].[Table1] pt
FULL OUTER JOIN [Archive].[dbo].[Table1] at ON pt.id=at.id
WHERE pt.Column1 <> at.Column1 OR pt.Column2 <> at.Column2..etc

To get only the differences

只获得差异

#1