Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003

    Talking Unanswered: Need DTS to overwrite data

    Newbie, so please bear with me.

    I'm trying to automate a daily extract from our Personnel System to SQL server using DTS.

    The Personnel system spits out a TXT file (containing approx 35 columns, 5000 records), which, is then picked up by DTS. I need this to overwrite the existing data, not duplicate it. However, after 1 successful load, if I try again I get a PK constraint error message.

    The PK is the employee's PIN, but data in the other columns can change (e.g. Address_1) which is why I need to overwrite the data.

    I have thought about importing the TXT file to a Temp Table, dropping the original table, then importing the data that way, but using DTS I cannot drop the table because of the constraints.

    Any help greatly appreciated.

  2. #2
    Join Date
    Feb 2003

    data warehousing

    i usually push data into a staging table, and then write the 'good records' into the fact table, and the 'bad records' get written to a exceptions table.

    its pretty straight forward

    you may need to fiddle with 'IDENTITY INSERT' in order to not get PK violations--

    it just depends on what kindof PK you use.

    i write a few views between the staging table and the fact table in order to filter out records that are already in the table, etc

    it would be pretty straightforward (if you have a simple PK) in order to do this

    basically i just issue a 'TRUNCATE TABLE' statement, and then i run most of my code in ActiveX script

    and then when it is done, you fire a stored proc that writes the records from staging to fact.

    cheers and good luck
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  3. #3
    Join Date
    May 2003


    thanks for the info Aaron - i will give it a try.

Posting Permissions

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