Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I have one database server, acting as the main SQL Server, containing a Table to hold all data. Other database servers come in and out (different instances of SQL Server). When they come online, they need to download data from main Table (for a given time period), they then generate their own additional data to the same local SQL Server database table, and then want to update the main server with only new data, using a C# program, through a scheduled service, every so often. Multiple additional servers could be generating data at the same time, although it's not going to be that many.

Main table will always be online. The additional non-main database table is not always online, and should not be an identical copy of main, first it will contain a subset of the main data, then it generates its own additional data to the local table and updates main table every so often with its updates. There could be a decent amount of number of rows generated and/or downloaded. so an efficient algorithm is needed to copy from the extra database to the main table.

What is the most efficient way to transfer this in C#? SqlBulkCopy doesn't look like it will work because I can't have duplicate entries in main server, and it would fail if checking constraints since some entries already exist.

share|improve this question
1  
I'm not placing this on the answers because it's not in C#. I think the really most efficient way, if you're really concerned about performance, is through database mirroring. – Renan 17 hours ago
One possibility is to do partial transaction replication (stackoverflow.com/questions/495680/…). Or just create an application that will maintain that task completely for you. – mipe34 17 hours ago
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". – John Saunders 17 hours ago

4 Answers

up vote 0 down vote

Here's how i would do it:

  1. Create a stored procedure on the main table database which receives a user defined table variable with the same structure as the main table.

it should do something like - 

INSERT INTO yourtable (SELECT * FROM tablevar)

OR you could use the MERGE statement for the Insert-or-Update functionality.

  1. In code, (a windows service) load all (or a part of) the data from the secondery table and send it to the stored procedure as a table variable.

  2. You could do it in bulks of 1000's and each time a bulk is updated you should mark it in the source table / source updater code.

share|improve this answer
You can't pass a common table expression as a variable in a stored procedure. Do you mean a user defined table variable? – tommy_o 15 hours ago
Yes, sorry. i'm updating my answer. – Uri Abramson 7 hours ago
up vote 1 down vote

You could do it in DB or in C#. In all cases you must do something like Using FULL JOINs to Compare Datasets. You know that already.

Most important thing is to do it in transaction. If you have 100k rows split it to 1000 rows per transaction. Or try to determine what combination of rows per transaction is best for you.

Use Dapper. It's really fast.

If you have all your data in C#, use TVP to pass it to DB stored procedure. In stored procedure use MERGE to UPDATE/DELETE/INSERT data.

And last. In C# use Dictionary<Tkey, TValue> or something different with O(1) access time.

share|improve this answer
up vote 0 down vote

SQLBulkCopy is the fastest way for inserting data into a table from a C# program. I have used it to copy data between databases and so far nothing beats it speed wise. Here is a nice generic example: Generic bulk copy.

I would use a IsProcessed flag in the table of the main server and keep track of the main table's primary keys when you download data to the local db server. Then you should be able to do a delete and update to the main server again.

share|improve this answer
up vote 0 down vote

Can you use linked servers for this? If yes it will make copying of data from and to main server much easier.

When copying data back to the main server I’d use IF EXISTS before each INSERT statement to additionally make sure there are no duplicates and encapsulate all insert statements into transaction so that if an error occurs transaction is rolled back.

I also agree with others on doing this in batches on 1000 or so records so that if something goes wrong you can limit the damage.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.