SQL Server Performance

DTS/Data Driven Query/DTS Lookups

Discussion in 'General DBA Questions' started by chfats, Dec 6, 2002.

  1. chfats New Member

    We are doing a huge job, which deals with text flat files (input) and
    DTS to transfer , edit/modify the data before transferring from
    flat-files to a production server...

    Here comes the point, here more details:

    (flat files are the new updated data which we want to load and import
    -using DTS- into a SQL2000 DB)

    I have one problem though (DTS);
    The flat-text-file contains a "kinds" of records, some that are NEW,
    and some that are to be edited/updated.
    By default it will get an error in the cases where the record should be
    edited because you get a double identity
    (since the DTS just tried to fetch the records from the text-file, nothing
    else), so my question is; how do I tell it
    to edit the records that already exists? I can probably do a lookup to check
    if it exists, then if it does execute
    some SQL to copy it in stead, then move on to the next record (hm, how do I
    tell it do move on?), but I am thinking
    there should be an easier way?

    Any help/direction would really appreciate, in this case i get adviced to use Data Driven Query and DTS Lookups, but we don't really manage it.

    My Best,

  2. DavidB New Member

    If I am understanding what you are trying to do, I would create a temp table to import the text file into. Once the data is in there simply write some T-SQL to delete the duplicate records from the final destination table. Example would be;

    delete Table1 where ID in
    (select ID from tmpTable2)

    Once that is complete you can do a full insert from the temp table into your final destination table.

    The delete and insert operation will be much faster than the update operation that you were proposing anyway.

    One suggestion anyway.


Share This Page