Synchronize with bulk insert – xSQL Data Compare

By default the database synchronization script that xSQL Data Compare generates contains one INSERT statement for every row that exists in the source database but does not exist on the target (destination) database. When the number of such rows is extremely large you can imagine that the synchronization script will likely get to be very large also and such script on a transaction will make for an extremely large transaction that is bound to create performance issues.

It is for that reason that xSQL Data Compare provides a little known “switch” called “Synchronize with bulk insert” which will change the default behavior and instead of generating one INSERT statement for each row will generate only one bulk insert statement for each table in the comparison making for a much more efficient synchronization script.

The “Synchronize with bulk insert” option can be found under the main menu Compare / Data Options.

In case you are not familiar with xSQL Data Compare – it is a simple tool that allows you to compare and synchronize the data in two SQL Server databases and supports SQL Server 2008, SQL Server 2005 and SQL Server 2000. It is free for SQL Server Express databases with no limitations and it is also free for other editions of SQL Server but with some limitations. You can read more and download your copy of xSQL Data Compare from our website at: http://www.xsql.com

Synchronize with bulk insert – xSQL Data Compare

When comparing and synchronizing data between two SQL Server databases if you have many new rows on one database that need to be “copied” over to the other database, by default the sync script that xSQL Data Compare generates will contain one INSERT statement for every new row being copied. Now, that is fine when the number of those insert statements is not very big but when you have hundreds of thousands of INSERT statements the script becomes very hard to manage and the performance will suffer.

It is for that reason that we included the ability to synchronize with bulk insert – to do that the user simply checks the “synchronize with bulk insert” option and specifies a folder where the bulk insert files will be stored temporarily. The resulting sync script is easy to manage and will perform a lot better.