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

[lingsn]

Instead of writting code to do the synchronization, may be can explore on the data replication in the SQL server to get it done automatically.

[alexyie]

i need user to do this synchronization on their own because they will do this daily basis on some master table and also transactions table. users are the one who capable to know when is the right time to do the synchronization, the sync process might be done once a day, twice a week or even several time a day based on their transactions volume that needed to upload to remote location. so basically its not local (main) server push to remote (branches) servers, but remote servers pull from local server

[szlamany]

It is extremely fast (and simple code as well) to produce a TEXT file that is tab delimited export from a table.

It is equally simple to write code to compare these two text files and output INSERT, UPDATE or DELETE statements based on the differences between the two text files.

And you can obviously take these INSERT/UPDATE/DELETE scripts and run them against your DB.

How big are these tables? How many rows? Are the two servers local to each other?

[Merrion]

You can do this with the BCP program (Bulk copy) which is a command line application that comes with SQL server.

Type

bcp /?

at teh command line to get the params needed

[alexyie]

How big are these tables? How many rows? Are the two servers local to each other?

several master tables (min 50 recs, max 1000 recs), and 6 daily transaction tables (1000++ recs each)

@Merrion: i will have a look at this "bcp" things first, thx

[techgnome]

Before you go the route of BCP.... the SQLClient namespace in .NET has a built-in method BulkInsert ... I recently used it to import DBF files just make a connection to the source data, fill a dataset/table... then make another connection to the SQL Server, and use the BulkInsert call, passing in the DataTable from the source. Slurrrp! In it goes. Fast, easy and simple.

-tg

[alexyie]

i cannot find out how bcp can do comparison between the source data & the destination before inserting the data. what i want here is not simply copy all data from source to destination, but to only copy the data that is added (and if possible, modified).

btw, @szlamany : the source server is local while the destination is remote location

[szlamany]

I find that the method I suggested is most appropriate.

Having a "text file" of each of the servers data be delivered to a central location where a simple VB program runs through them comparing row to row.

New rows in one file cause INSERT statements to be written to a TEXT FILE. If they are missing (and it's what you want) then a DELETE statement is written to the TEXT FILE. Otherwise if the two primary keys of the row match a column-by-column compare is done (of the text file) and an UPDATE statement is written to make the databases match.

All these are written to a TEXT FILE that is then executed on the "destination server" using ISQL/OSQL (command line SQL query executor).

I find that this method of getting tables to match when the servers are remote is best. Being able to see the "TEXT FILE" of what updates will occur is helpful. Archiving these text files makes lots of sense.

This method also automates nicely - couple of .BAT files that can be run from a scheduled job or even the server agent.

[alexyie]

using 2 text file, how to compare if row in text1 exist in text2? should i do two times looping for this (loop for text1 lines and text2 lines)? or is there any better and faster way to do the checking?

[szlamany]

Both text files need to be sorted in the exact same primary key order - this is paramount.

Otherwise - the logic is simple.

Pseudo-code:

1) Get a record from text file 1
2) Get a record from text file 2
3) If the primary key's in both records exist then
...compare columns
......Set a flag to get a record from both text files 1 and text file 2
4) If the primary key of text file 1 is less then text file 2
...then text file 1 row does not exist in text file 2
......Deal with this fact - set a flag to get a row from only text file 1
5) If the primary key of text file 2 is less then text file 1
...then text file 2 row does not exist in text file 1
......Deal with this fact - set a flag to get a row from only text file 2

dealing with the fact is either an INSERT or DELETE statement - based on which table you feel is the production data...

[alexyie]

for pseudo-code 1) and 2), how can i get the record from the text file? read line by line from text1, and for each line I do another line by line reading from text2 to find if there is any matching primary key?
wont this become very slow if i got alot of records?

[szlamany]

This is a proven technique - used by us on tables with millions of rows.

The tables must be sorted in primary key order.

If all the rows happen to exist the same between both tables then you read a row from text file 1 and text file 2 - they match - you continue.

There is nothing faster than doing this.

[alexyie]

both textfiles having same rows cannot guarantee that this two textfiles is the same right? it is posible that text1 (source) have added a row and deleted another one (which makes the number of rows the same).
can you give a little logic of how to compare this two text file and determine which one i need to insert/update/delete from destination database
thx b4

text1
-A0001 Alpha
-A0002 Alexander
-B0004 Bingo
-C0002 Charles
-C0004 Caroline

text2
-A0001 Alpha
-A0002 Alexis
-B0004 Bingo
-C0003 Charlie

[szlamany]

I took your two files and created .TXT files on my C: drive...

This does it:

Private Sub Form_Load()
 
Open "c:\file1.txt" For Input As #1
 
Open "c:\file2.txt" For Input As #2
 
Dim booGet1 As Boolean, booGet2 As Boolean
 
Dim str1 As String, str2 As String
 
booGet1 = True
booGet2 = True
 
While booGet1 Or booGet2
 
    If Not EOF(1) And booGet1 Then Input #1, str1
    
    If Not EOF(2) And booGet2 Then Input #2, str2
    
    Debug.Print str1; " * "; str2
    
    booGet1 = False
    booGet2 = False
    
    If Left(str1, 6) = Left(str2, 6) Then
        Debug.Print "Same keys - we should compare rows and make UPDATE statement if needed"
        booGet1 = True
        booGet2 = True
    Else
        If Left(str1, 6) < Left(str2, 6) Then
            Debug.Print "Exists in file1 - need to add"
            booGet1 = True
        Else
            If Left(str1, 6) > Left(str2, 6) Then
                Debug.Print "Exists in file2 - need to delete"
                booGet2 = True
            End If
        End If
    End If
 
    If EOF(1) And EOF(2) Then
        booGet1 = False
        booGet2 = False
    End If
 
Wend
 
End Sub

displays this in the immediate window - note I assumed that one file was primary - meaning if it existed in that file we would add, if it did not exist in that file we would delete. That logic is up to you - but this is the very simple logic of comparing two text files for PK's and keeping each one aligned to the other.

-A0001 Alpha * -A0001 Alpha
Same keys - we should compare rows and make UPDATE statement if needed
-A0002 Alexander * -A0002 Alexis
Same keys - we should compare rows and make UPDATE statement if needed
-B0004 Bingo * -B0004 Bingo
Same keys - we should compare rows and make UPDATE statement if needed
-C0002 Charles * -C0003 Charlie
Exists in file1 - need to add
-C0004 Caroline * -C0003 Charlie
Exists in file2 - need to delete