Correct method for using DTS to append table through CSV import

Posted on 2007-10-16
Last Modified: 2013-11-30
I can import a CSV fine using the text driver  in DTS fine.  My problem is that in order for it to not duplicate the data I have to issue a delete all command prior to the import.  If I dont then it just doubles the data up.

Can anyone suggest a method whereby the data would be appended / added using DTS.

Thanks in advance.

Question by:andrewmilner
    LVL 15

    Accepted Solution

    Not sure I understand. If you do not delete data before doing the data transformation, it will append data. But you say you don't want to do that because you get duplicate data. Then you ask if there is a way to append data. I'm confused.

    Firstly, I would recommend doing a TRUNCATE TABLE instead of DELETE.
    Secondly, if you mean is there a way to only insert 'new' rows (those that don't match a current 'key', then not efficiently. It could be done by doing the transformation through an ActiveX script in which you open another connection to the database and run a query on a row-by-row basis to see if the record already exists. I would not recommend it though as it would involve a lot more resources that truncating the table and bulk inserting the data back into it.
    LVL 8

    Assisted Solution

    I think the way you want to design your DTS is possible but it will take lot of resources and it will just become a tidious process.

    The best way is to delete the table before the import of CSV files.

    It works fine and there 100% gurantee of no duplication as you are deleting the data from the table prior to import

    Please let me know if this helps.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now