Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    22

    Unanswered: how to copy the good records and identify the bad ones?

    Hello guys

    In a job of migrating from an old database to a new one (with other structure, other server, other version) i'm copying from the source old tables and inserting into the new destination tables.
    The problem is that some records have inconsistencies (of any kind) and thus are not inserted due to foreign key, not null, etc validation.
    When a problem occurs none record is copied! and there is my question: How can i perform the copy in wich it copies the good records (without inconsistencies) and leave aside the bad records.
    I also want to know wich were not copied and better if in the copy process those were put in a temp table or exported to excel for further analisys o its data.

    i'm using this model of "migration":

    BEGIN TRY

    INSERT INTO DESTINTATION_TABLE (
    col_d1,
    col_d2,
    col-d3,
    ...)
    SELECT
    col_s1,
    dbo.some_function(col_s2),
    col_s3 * 100,
    ...
    FROM SOURCE_TABLE join <other_table> ... where <some filters>


    END TRY

    BEGIN CATCH
    print ERROR_MESSAGE()
    END CATCH

    (for now, with try/catch i've only get to know the error occurred, if some)


    Thanks a lot in advance
    Last edited by carloco; 09-24-12 at 15:50.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest you consider "editing" the input data and do not attempt to insert things with errors.

  3. #3
    Join Date
    Jul 2006
    Posts
    22
    Quote Originally Posted by papadi View Post
    Suggest you consider "editing" the input data and do not attempt to insert things with errors.
    no, i'm not trying to insert records with errors, i try to somehow filter them.
    I want my code (or other suggested) copy (select+insert) all the records from source table to destination table, BUT if some record has data that produce error at the moment of insertion, do not copy that specific record and continue copying the others.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by carloco View Post
    no, i'm not trying to insert records with errors, i try to somehow filter them.
    I want my code (or other suggested) copy (select+insert) all the records from source table to destination table, BUT if some record has data that produce error at the moment of insertion, do not copy that specific record and continue copying the others.
    I would suggest using SSIS and create a package with data transformations. There are a couple oledb drivers which provide limited error handling. However, I think you will need to create specific error handling for different situations; like datetime errors (outside range), datatype conversion errors, etc.

  5. #5
    Join Date
    Jul 2006
    Posts
    22
    Quote Originally Posted by corncrowe View Post
    I would suggest using SSIS and create a package with data transformations. There are a couple oledb drivers which provide limited error handling. However, I think you will need to create specific error handling for different situations; like datetime errors (outside range), datatype conversion errors, etc.
    i'm not sure about ssis because source is on sqlserver 2000. I'm working on ss2008 but the final implementation would be on ss2000 (source) and ss2012 (dest.)

    I'm thinking of using cursors but just as last alternative...

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Why not write some views /stored procs that perform the required manipulation and validation of your source tables, and only output the records that pass? You could then write the reciprocal objects to display the faulty records that are in need of attention.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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