Export DTS

I have a SQL database on a local server that I need to recieve data from a external database. Both machines are SQL 2000 the local version just needs data from the web version. The local version has a few different tables but besides that pretty much the same.

I want it to export data from the web version daily and just add whats new. I tryed it and it just doubled the data I had it drop the tables and create new ones but it did not do that.

Is there a good how-to on the web or somewhere?

Thanks for your helpThe simplest way to do it is to truncate (NOT drop) the table on the local machine before you import the full set of data. Add an 'Execute SQL' task to your package that has a SQL statement of 'Truncate Table xyz'.

Once that is complete, continue as before and import the data.

If you want to keep the exisiting data in the local table and only add new data from your web database server, then you would probably need to set up a linked server between the 2. The linked server would enable you to only import rows that are not in the destination server (by specifiying the rows to add in a SQL statement in the 'Source' section of your Transform task).

If your Web server db contains all the data you need, then the truncate option is probably the easier way to go. However, it does mean that your local table will not have data for couple of seconds/minutes (between the truncate and the end of the data pump).

Lionel|||Why dont use DTS package to transfer data between the tables and add a Data Driven Query Task in order to append only the required records

Balaji