Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: Error handling in SQL insert statement

    I have a loop statement inside a DTS package task where it needs to filter each row/record.

    Here's the logic:

    Do while not eof
    insert into destinationtable
    select * from sourcetable

    if cannot be inserted (bcoz of constraints like null, data type, etc.) then
    insert in another destination table w/ no constraint
    else
    continue w/ insert
    end if
    loop

    Can you show me the SQL Query sntax for this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A long shot: are you on 2005?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    i'm using MS SQL 2000.

    another question related to this.

    how can i get the error no. of a certain sql statement?

    i'm using sql query analyzer, here's my code:

    declare @errorno as int
    set @errorno='insert into table2 (select * from table1)'
    select @errorno

    for example the insert statement results to an error...can i assign the error no. to the variable @errorno? and view it by typing 'select @errorno'. and what if the insert statement does not result to an error? what will be the value of the @errorno?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The best way to do this is to move all that complex logic out of DTS. DTS sucks a$$ (lots of them).

    Create an unconstrained staging table.
    Use DTS to get data from the filesystem into the staging table.
    Call a proc that cleans and screens the data in the staging table.
    Insert the data into the production tables.

    Seriously - trying to handle multi path evaluations and error handling in DTS is like throwing valuable development time down the drain. If that doesn't persuade you, DTS is deprecated in SQL 2005 (and that isn't even the current version of SQL Server) so you'll have to rewrite the thing one day anyway.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely.
    Keep business logic OUT of DTS and SSIS packages.
    The best way to handle this is to drop the data into a staging table and run a sproc to verify it, cleanse it, parse it, and distribute it to production tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2005
    Posts
    119
    thanks for your inputs. that makes sense.

Posting Permissions

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