DTS question.

Hi,

i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.

Thanks in advance.Originally posted by johan_gr
Hi,

i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.

Thanks in advance.

If you have a date and time value it could be done in your query..

WHERE date = > 'dateyourchecking'

Ken|||GA_KEN is right; it will generally mean substantially more work for you than just using the DTS Wizard however. For each table that you want to port to SQL Server, you will have to write a query (after determining what logic to use to assure that duplicate records are not brought across). Although often times this is a pretty straightforward task, there are a lot of complicating factors. In some cases, it may be preferable to simply truncate (delete * from mytable in Access) the existing table and re-load it from scratch.

Good luck,

Hugh Scott|||but im not an expert on that, i dont have time for that kindof thing...

cant you just reimport the whole table from scratch?

or do some sort of 2-stage import, where you just import recently changed records and then you push these into the production server on the database side (by using a stored procedure-- insert into dbo.productionTbl select * from AccessImport where date > (Select Min(Date) from myDateCatcher)