CODEWITHSUNDEEP
sqlsql-server
user1059903
user1059903

Reputation: 269

Verify a row in one table matches a row in another table

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:

  1. Find a record you want to archive
  2. Verify it isn't already archived
  3. Copy the data over
  4. Verify all the data is copied over
  5. 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.

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

(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 ;)

Upvotes: 0

Views: 1864

Answers (2)

Mack
Mack

Reputation: 2552

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!

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

Upvotes: 1

Milney
Milney

Reputation: 6417

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

Upvotes: 0

Related Questions