I have a few tables that update everyday with fresh data.
The contents of the archive table are truncated before the import starts, the current data is then copied to the archive table and then truncated ready for the fresh data.

so I have two tables, one with current data, one with yesterdays data. I need to write queries to show if any data has changed between the two.

CREATE TABLE [TblBond] (
[issuer] [varchar] (50) NOT NULL ,
[maturity_date] [datetime] NOT NULL ,
[coupon] [numeric](30, 10) NOT NULL ,
[currency] [char] (3) NOT NULL ,
[bond_type_code] [varchar] (12) NOT NULL ,
[sec_id] [int] NOT NULL ,
[otr] [int] NOT NULL ,
[identification_str] [varchar] (60) NULL ,
[description] [varchar] (30) NULL ,
[settlement_date] [datetime] NULL ,
[buy_sell] [varchar] (4) NOT NULL ,
[trade_amount] [numeric](38, 10) NOT NULL ,
[accrued_interest] [numeric](38, 10) NOT NULL ,
[remark] [varchar] (255) NOT NULL ,
[counterparty] [varchar] (20) NOT NULL ,
[booking_entity] [varchar] (20) NOT NULL ,
[keyword] [varchar] (255) NOT NULL ,
[trans_id] [int] NOT NULL ,
[trade_id] [int] NOT NULL ,
[trade_status] [varchar] (12) NULL ,
CONSTRAINT [PK_TblBondData] PRIMARY KEY CLUSTERED
(
[trade_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO


Trade_id is the PK, the archive table is the same structure but called TblBond_archive


I have got a query that works, but is very lengthy and will need amending if the columns change at all (and as im dealing with traders, im sure they will add/remove things!)

query is like dis :

SELECT
TblBond.trade_id,
'Issuer' AS Field_Changed,
cast(TblBond_Archive.Issuer as varchar) AS Old_Value,
cast(TblBond.Issuer as varchar) AS New_Value
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.Issuer<>[tblbond_archive].[Issuer]

UNION

........ and goes onto next field. for another 19 columns!!

There must be a more eloquent way of coding this? anyone any ideas? im using SQL server 2000, so i do have some schema tables i can call on if needed.

Any ideas would be great as I have about 10 tables using similiar principals and tis a pain to code it all!!

Regards

The by far easiest and fastest way for doing this would be the use of a timestamp column in your live table and an appropriate datetime in the archive table. It gets updated automatically on every update. You can get the updated records with a simple:

SELECT blablabla
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.updated_timestamp > TblBond_Archive.updated_datetime

Thanks Apel, in an ideal world this is what i would have done in the first place, but alas, this data is actually sourced from a rather orrible sybase system, which I can't make any DDL changes to, as all the sybase dev team where let go a few months back!!

so no go on the timestamp column! all I can do is import the tables from sybase into my sql server for the purposes of reporting.

Also that method wouldn't enable to keep an audit trail of what changes have happened to each trade. the way i described allows me to run that big mother query everytime data is imported and copy results to an audit table. Which stores the PK, fieldname, old value , new value , and the datetime the change was detected. I also tacked on 2 other union select queries to pick up new or deleted trades.

I ended up writing a wee bit of vba code (which i ran in MS access) to help me create the SQL code for the big query! worked nicely. but still think there must be a more eloquent way to code this.