update instead of insert | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

update instead of insert

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
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]
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.
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
]]>