Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    13

    Wink 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
    (
    [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
    no need to panic - its just 1's and 0's

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    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

  3. #3
    Join Date
    Jan 2004
    Posts
    13
    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.
    no need to panic - its just 1's and 0's

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •