export multiple (same) dbs and import into one automatically

Question

I need to write a program or script which a client can run to do the following. I have an application which runs offline on multiple computers which is exactly the same but the data will differ. Let's call this SourceDB. Therefore, there is SourceDB1, SourceDB2, and SourceDB3.

Then there is a server which hosts the main application which has the exact same table structures..let's call this DB TargetDB.

I need to be able to take the data from SourceDB1 and append it to TargetDB. I need to be able to take data from Source DB2 and append it to TargetDB. Same goes for SourceDB3.

How can I do this? This is currently a MS SQL 2008 database.

Each time there worker brings in SourceDB1, I need him to be able to run a batch or a program which will export the data. Then he can copy that file, and run it on the server to import the data to TargetDB.

Any ideas?

Answer 1

This is what Merge Replication in SQL Server is designed to do, but it can be tricky to get setup correctly the first time around. The other option would be to create a SSIS package that dumps the data from the SourceDB's into a flat CSV file that can be copied to a NAS\Fileshare. Then another SSIS package would be created for loading the data into the TargetDB from the CSV flat files in the NAS\Fileshare. This is how a lot of ETL processes work to pull data from source systems into staging tables for data warehousing.

You shouldn't need to write any customized code to do this kind of task. There are already tools in SQL Server that are made to handle this work.