CODEWITHSUNDEEP
sql-servert-sql
Justin808
Justin808

Reputation: 21512

TSQL Comparing 2 tables

I have 2 tables in 2 database. The scheme for the tables is identical. There are no timestamps or last updated information. Table A is a live table, that is, it's updated in "the" program. Update records, insert records and delete records all happen in Table A. Table B is a backup made weekly. Is there a quick way to compare the 2 tables and give me results similar to:

I | 54
D | 55
U | 60

So record 54 in the live table is new, record 55 in the live table was deleted, record 60 in the live table was updated.

This needs to work in SQL Server 2008 and up.

Fields: id, first_name, last_name, phone, email, address_id, birth_date, last_visit, provider_id, comments

I have no control over the scheme. I have read-only access to Table A, read-write to Table B.

Would it be easier to store a hash of each Table A's rows rather than a full copy of the table? Generally speaking I need to know what rows have been updated/inserted and deleted without a build in timestamp. I have the weekly backup table to look at but I could create a hash table if needed.

Upvotes: 0

Views: 296

Answers (3)

T I
T I

Reputation: 9933

Using two full joins the first one isvused to check just for id existance and identify inserts and deletes the second would be used for row equality.

In the example I have used checksum for simplicity but I recommend you read up on the cons of using it and consider alternatives like hashbytes or checking each column for equality

Select id, checksum(*) hash
Into #live
From live.dbo.tbl

Select id, checksum(*) hash
Into #archive
From archive.dbo.tbl

Select l1.id,
    Case when l1.id is null then 'd'
              when a1.id is null then 'I'
              when a2.id is null then 'u' end change_type
From #live l1
Full Join #archive a1 On a1.id = l1.id
Full Join #archive a2 On a2.id = l1.id
                                    And  a2.hash = l1.hash

Upvotes: 1

dbaduff
dbaduff

Reputation: 1

If there is a column or set of columns that can uniquely identify each row, then a series of sql statements could be written to identify the inserts, updates and deletes. If there isn't a unique row identifier or the unique identifier (for example, one of the columns that makes it unique) changes, then no.

Upvotes: 0

thursdaysgeek
thursdaysgeek

Reputation: 7926

I'm going to recommend a tool, but it's not free, although it has a fully functioning 30 day trial period. If you're going to compare data in SQL Server tables, look at Red Gate's SQL Data Compare. It's not cheap, and it will pay for itself many times over. (If you need to compare schemas, their SQL Compare does that.)

Barring that, having a third table, where you write a compare query and select those in one table and not the other (with a field indicating that), those in the other table and not the first, and then comparing field by field to find those different -- well that should work too. It will take longer, but if it's just one one table, the time it takes to write that code should be less than what you'll pay for the Red Gate tools.

Upvotes: 0

Related Questions