Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: How to keep import work going when on error?

    When I import data from Access in to the SQL server, SQL it gives an error because the
    duplicate record (I set those field to primary) and stops. However, is there a way to let the work keep
    going for the rest of the records?

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: How to keep import work going when on error?

    The only way to avoid that is to drop the primary key (the column or columns combinations participating in a primary key must be unique) , import data, eliminate the duplicates then reset the primary key.

    Originally posted by emk662
    When I import data from Access in to the SQL server, SQL it gives an error because the
    duplicate record (I set those field to primary) and stops. However, is there a way to let the work keep
    going for the rest of the records?
    Steve

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its good practice not to import directly into your primary data tables. Import into a staging table first. Your staging table should just be a heap with no restrictions on what data can go into it. You can then cleanse the data as your distribute it to your primary data tables, mark records that have issues, and do other trouble-shooting.

    blindman

  4. #4
    Join Date
    Jul 2003
    Posts
    2
    To import directly is actually my purpose, because I like to use the primary key to reject those unclean data. So, is there still a way.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not without dropping your primary key and then cleaning the data in place before adding the primary key back in. Cleaning data, such as identifying duplicates, requires some sort of busines logic, and you can't incorporate much business logic in a straight data load. You can put as much logic as you want in a procedure that transfers data from a staging table to a data table. It is really the best practice, because it also allows you to identify exactly which rows were kicked out or failed to process to make sure that the process is working correctly and you are not losing valuable data.

    blindman

Posting Permissions

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