Unanswered: Returning fields that have changed since last import
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
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
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 :
'Issuer' AS Field_Changed,
cast(TblBond_Archive.Issuer as varchar) AS Old_Value,
cast(TblBond.Issuer as varchar) AS New_Value
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
........ 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!!
no need to panic - its just 1's and 0's