UPDATE/ADD to table from remote table

[robsuttonjr]

I have imported a table from a btrieve database into SQL server 7.0. I tried using DTS to update the data SQL server from the updated table in the remote btrieve database. But it merely appends all the data on top of what is already there (so table is has duplicate data). I would like to know how I can automate updating/adding data from btrieve database to the copy of the table in SQL server.

 

[AshleyRickards]

When you go through the DTS import wizard and get to the tables to import, select the transformations ... and select 'Drop and recreate destination table'.
When you get to the 'Save, schedule, replicate package' section choose 'Save DTS Package' at the bottom. You can then schedule the package to run daily at a certain time to refreesh your data or execute the package manually without having to go through the import manually every time.

HTH Ashley L Rickards
SQL DBA

 

[robsuttonjr]

Thanks, that works and I wonder if there is a quicker way to either update or add new items. Doing the drop and create method takes a long time.

 

[AshleyRickards]

If you had a certain field in the btreive db that could be used to pull by, such as a status field indicating either a new record or updated record. You could use a query to import and append to existing db. Ashley L Rickards
SQL DBA

 

[sguslan]

Hi

Think of using a timestamp[date-time-milliseconds] or datetime field in the btrieve source table. As a new record is inserted or an old one updated have your application update the timestamp field or may be use a trigger to do so. Then, when u schedule the DTS to get data, program it to get data with timestamp > last run timestamp..

I use this all the time .. Good luck
Salim