Copy remote SQL table & manage key violations!!

[leedo2k]

Hi,

I am using an SQLcommand to copy table rows from a remote sql table to a local table with the same schema. However, sometimes I get primary key violation ecxpetions.

To resolve that I load the remote table rows into memory in a DataTable. Then I would loop through the rows and issue a "delete from" sql command using another sql command where the primary keys are equal. However, I noticed that this is not very efficient and seems to take a long time. Sometimes it even timeout the sqlcommand. I know I can increase the commandtimeout property of the sqlcommand. But I still believe there is a more efficient method.

I also want to know exactly how many rows were affected or deleted.

Could you please help me with that.. thanks


Here is my code:

    Dim keycommand As New SqlCommand
                                Dim RunQuery, RowsAffected As Integer
                                keycommand.Connection = sourceConn
                                keycommand.Connection.Open()

                                RowsAffected = 0
                                For Each Row As DataRow In CCMdataTable.Rows

                                    'Delete all records from remote table that  match the already existing PKIDs in local Database
keycommand.CommandText = "SET LOCK_TIMEOUT 1000 SET NOCOUNT ON delete from dbo.syscdr where pkid='" & Row.Item("pkid").ToString & "'"
                                    keycommand.CommandTimeout = 300
                                    Try
                                        RunQuery = keycommand.ExecuteNonQuery()
                                    End Try
                                Next