Data Transfer between SQL Server

[alexyie]

i want to create an application that can do syncronization (data transfer) between 2 SQL server (the dbstructure in each server are identical, lets say db1 and db2 for the database in these server). is there any method to compare the data stored in table in db1 and db2 (table1 and table2), and if different found in table2, then copy from table1 to table2.
i know that doing looping for these table and comparing each of the records can do this job, but it is very troublesome and ofcourse take a very long time.
and then i found out about dataset / datatable merge method, but i cannot actually get this method to work. is there anyone can help me with this datatable.merge methodology or any other better method to use.

i have already tried something like this:

Private Function CopyData() As Boolean
        Dim strSQL As String = ""
        Dim sqladpData As System.Data.SqlClient.SqlDataAdapter
        Dim dtSrc As System.Data.DataTable 'source data table
        Dim dtDest As System.Data.DataTable 'destination data table
        Dim dtChanges As System.Data.DataTable 'changes data table
        Dim drChange As System.Data.DataRow
        Dim dcPrimary(0) As System.Data.DataColumn
        Dim sqlcbrUpdate As SqlClient.SqlCommandBuilder
 
        '---- Company ----
        strSQL = "Select * From Company order by Code"
        'source data
        sqladpData = New System.Data.SqlClient.SqlDataAdapter(strSQL, g_objSrcConn)
        dtSrc = New System.Data.DataTable
        sqladpData.Fill(dtSrc)
        'destination data
        sqladpData = New System.Data.SqlClient.SqlDataAdapter(strSQL, g_objDestConn)
        dtDest = New System.Data.DataTable
        sqladpData.Fill(dtDest)
        'setup primary keys
        ReDim dcPrimary(0)
        dcPrimary(0) = dtSrc.Columns(0)
        dtSrc.PrimaryKey = dcPrimary
        dcPrimary(0) = dtDest.Columns(0)
        dtDest.PrimaryKey = dcPrimary
 
        'merge source & destination to get changes
        dtDest.Merge(dtSrc, True, MissingSchemaAction.Ignore)
    End Function

but i cannot update the merge result (newly added row from source) to the destination table in destination server