DTS/Data Driven Query/DTS Lookups | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS/Data Driven Query/DTS Lookups

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, Thanks!
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. David