DTS add and replace rows

  • hi, I am using sql2000. how do i design this. I have to make a dts packag where the data from the csv has to be either add or replaced to the database. I cant delete the table before loading since this would get rid of the unchanged the data. The csv will contain the new and changed data. How do i write a query or somehow insert the new and changed data to the sql 2000 database.

  • What you are talkin about here is a basic datawarehousing technique. This is what you should do:

    1. Create a table that matches the target table. We will call this a staging table.

    2. Truncate the staging table.

    3. Run a sql statement to update or insert into the target table using t/sql sourcing the satging table. If you can tell which rows were updated (ie if you have an update field) you can run update, not found insert etc...

    The advantage of this technique is being able to trace the data right the way through in case of any queries.

    You can code this into a package, by connecting the staging table to the csv file and then calling a stored procedure with the target table population logic.


    ------------------------------
    The Users are always right - when I'm not wrong!

Viewing 2 posts - 1 through 1 (of 1 total)

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