November 24, 2008 at 7:21 am
SQL 2005
Hi,
I need to compare AND audit the changes between 2 tables (one held on SQL server ‘A’ database and another on SQL Server ‘B’ database) where they have the same name (ASSETGROUP is used in the example below).
I have to compare the values of each row (based on the same key), based on INSERTIONS, UPDATES and DELETIONS.
A typical UPDATE query:
go
UPDATE dbo.ASSETGROUP_TempTable
SET
--ALL FIELDS
[NAME] = A.[NAME],
GROUPID = A.GROUPID,
AUTONUMBER = A.AUTONUMBER,
AUTONUMBERSEQUENCE = A.AUTONUMBERSEQUENCE,
BARCODENUMBERSEQUENCE = A.BARCODENUMBERSEQUENCE,
AUTONUMBERBARCODE = A.AUTONUMBERBARCODE,
ASSETTYPE = A.ASSETTYPE,
DATAAREAID = A.DATAAREAID,
RECID = A.RECID
FROM
dbo.ASSETGROUP B INNER JOIN PETSQL2VM.ax41kwtritst.dbo.ASSETGROUP A ON
--PK FIELDS
B.GROUPID = A.GROUPID AND
B.DATAAREAID = A.DATAAREAID
WHERE
--NON PK FIELDS
B.[NAME] <> A.[NAME] OR
B.AUTONUMBER <> A.AUTONUMBER OR
B.AUTONUMBERSEQUENCE <> A.AUTONUMBERSEQUENCE OR
B.BARCODENUMBERSEQUENCE <> A.BARCODENUMBERSEQUENCE OR
B.AUTONUMBERBARCODE <> A.AUTONUMBERBARCODE OR
B.ASSETTYPE <> A.ASSETTYPE OR
B.RECID <> A.RECID
My problem is that the NON-PK (Primary Key) fields may change in either table. I need to automate the following tasks:
1.Search for all matching columns within both tables
2.Build a temporary table containing ALL of the matching fields
3.Build a similar query to the above example but overwrite the ‘ALL FIELDS’ and ‘NON PK FIELDS’ sections of the query according to the matching fields found
Any ideas?
NOTE:
This is a requirement for several tables, some with MANY columns
Thanks in advance,
November 24, 2008 at 8:54 am
I'm not sure I understand the requirements here.
Searching for matching fields and non matching fields are conflicting issues. It sounds like you are trying to do both at once.
Can you break this down a little simpler? Is this for checking to see if things are in sync or is this auditing changes?
November 24, 2008 at 9:13 am
To answer your question, I'm syncing field name's and auditing 'changed' values based on the Primary Key (This will be the same in both tables)
I'm NOT concerned with non-matching fields... Once I've established which fields exist in both tables, I then want to audit the field value changes (In Table_1 against Table_2).
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply