SQL Server Performance

update instead of insert

Discussion in 'SQL Server DTS-Related Questions' started by boutwater, Apr 14, 2005.

  1. boutwater Member

    Hello,

    I have a db on my desktop and a db on our server that are the same. I want to get the records off the server for a table (A) and add them into the same table in have on my desktop. I've tried using DTS to import a table, but i always get the error saying that the opperation failed due to not allowing duplicate primary key records. I realize this, but i don't wan't duplicate keys. I either want it to post over these records, or to skip them and go on with the rest of the import. How can i do this? Thanks in advance,

    Ben
  2. dineshasanka Moderator

    There are few ways you can do this
    1. If you want to countinue with the DTS, you have to use Mulyi phase data pump method
    in which you can skip the unnessary records.
    Mor eInformation
    http://www.sqlservercentral.com/col... do this? Thanks in advance, Ben [/INDENT]
  3. boutwater Member

    Thanks. That got me closer to a solution. I'm having trouble writing the vb script. Here is what i tried. It parses and tests okay, but then when i try to run it, i get the same error stating that:
    ---------------------------
    Transform Data Task: undefined
    ---------------------------
    The task reported failure on execution.

    The statement has been terminated.

    Violation of PRIMARY KEY constraint 'PK_Address'. Cannot insert duplicate key in object 'Address'.


    ---------------------------
    OK
    ---------------------------

    Here's my code:

    '**********************************************************************
    ' Visual Basic Transformation Script
    '************************************************************************

    ' Copy each source column to the destination column
    Function Main()
    DTSDestination("Line4") = DTSSource("Line4")
    DTSDestination("DateTimeLastChanged") = DTSSource("DateTimeLastChanged")
    DTSDestination("Country") = DTSSource("Country")
    DTSDestination("PostalCode") = DTSSource("PostalCode")
    DTSDestination("State") = DTSSource("State")
    DTSDestination("City") = DTSSource("City")
    DTSDestination("Line3") = DTSSource("Line3")
    DTSDestination("Line2") = DTSSource("Line2")
    DTSDestination("Line1") = DTSSource("Line1")
    DTSDestination("AddressID") = DTSSource("AddressID")
    Main = DTSTransformStat_OK
    End Function

    Function InsertFailureMain()
    InsertFailureMain = DTSTransformStat_SkipRow
    End Function

    Thanks for your help.
  4. dineshasanka Moderator

    I think this is transform failure not a insert failure
    Check this
    http://www.sqldts.com/default.aspx?282,4so u can write folowing code


    Function TransFailureMain()
    DTSGlobalVariables("CurrentRow").Value =
    DTSGlobalVariables("CurrentRow").Value + 1
    DTSGlobalVariables("BatchesComplete").Value = 0
    'write status to log file
    logfile.writeline "Could not insert source data at row " &
    DTSGlobalVariables("CurrentRow").Value
    'do not insert the row at destination
    TransFailureMain = DTSTransformstat_SkipInsert
    End Function

Share This Page