help with SQL compare/update

  • Hi

    Today I've been tasked with something quite urgent and I'm more of an Admin person rather than programmer. I have an idea what I need to do but would be great to hear how other people have approached this. Unfortunately I cannot make use of Triggers in this situation.

    Table A:

    ClientNo, RespEmployer, LastModified + another 50 columns

    Table B:

    ClientNo, RespEmployer,Period.

    Table A is part of an off shelf accounting package, when the RespEmployer column changes I need to be able to record this change in TableB(custom table) and  also update the Period column with the current accounting period.

    I plan to write a DTS package that runs nightly and extracts all data from Table A where LastModified date has changed, dump this data into a static temporary table.  Then compare between Table A and Temp table and where the RespEmployer has changed in Table A then I update Table B with this new information and some further updates.  Am I right in thinking that I need to write a cursor to do this or am I missing something very simple here?

    Appreciate any feedback/suggestions...

  • You can use triggers

  • >>Unfortunately I cannot make use of Triggers in this situation.<<

    You have to use either Triggers or third party tools 


    * Noel

  • If you can't use triggers and want to record all changes to Employer, you could first record current values of ClientNo/RespEmployer - for ALL clients - in the table B as a starting value. Then run a job nightly that would find differences in RespEmployer between the table A and table B, and record these changes to table B. You'd have to make sure that the value in table A is always compared with the NEWEST value in table B only. I would write a stored procedure that does all that and use the job to schedule it accordingly.

    This would not require cursors, everything can be done with a set-based SQL... well, unless there is a hitch in the "and some further updates".

    HTH, Vladan

  • Keep in mind that if you run this once a day you can't be sure to record all changes since if your data changes more than once in a day you will not know that.  If this is supposed to provide an audit trail your once a day solution will not do that.  If you are only attempting to keep a log of the most current change then you can do it something like this....

    --update already existing records

    Update Tableb

    set  Period = AccountingPeriod

    from TableA a where Tableb.ClientNo = a.ClientNo and Tableb.RespEmployer = a.RespEmployer and

    LastModified between date1 and date2

    --insert records that don't exist

    Insert into Tableb

    select ClientNo, RespEmployer, Period from TableA a where LastModified between date1 and date2 and not exists (select 'not exists' from Tableb b where a.ClientNo = b.ClientNo and a.RespEmployer = b.RespEmployer)

    hope this helps

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply