Results 1 to 4 of 4

Thread: DTS question.

  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Posts
    126

    Re: DTS question.

    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

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    109

    or you could do replication

    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)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •