Bulk transfer of data from SQL to SQL (maintain relationships)

So we currently have the requirement to extract data from 3 separate systems, into 3 'staging databases'. These staging database share the same table layout.

Once all 3 staging databases are populated, a extract tool takes the data, validates it, and moves it to a 'final' database, with relationships persisted.

Currently there are 8 or so tables, each with ~100k开发者_如何学C rows. So not very much data. However it takes quite a while to transfer.

An example of the data would be Blog Posts to Tags, with tables like this:

- Blog Post
-- ID

- Tags
-- ID

- Blog Post Tags
-- Blog ID
-- Tag ID

So when extracting the data, we need to keep the relationships, even though the ID's will change. so Insert (if not exists) Post, Get ID, Insert (if not exists) Tag, Get ID, Insert Blog Post Tag, with ID's.

Currently I'm using LLBLGen Entities, and fetching lot's of 10,000 Blog Post Tags, checking to see if the Post exists, if not insert, and the same with the tag. But due to the huge amount of queries per Blog Post Tag it takes quite a while.

Another way I've tried is Do all Blog Posts, Do All Tags then do all Blog Post Tags. However, this does not allow the task to be re-run if it fails (as the previous method will delete each Blog Post Tags as it's transferred.

We are using C# 3.5, SQL 2008, LLBLGen 2.6 (however can change this).

What would be the best way to go about this?

Personally I would look at doing something with SSIS or using an export to file and import via bulk copy. Those would be your best bets for speed. The other thing you could consider is setting up log shipping to keep your staging datbase in sync with live but I'm not sure that would meet your requirements.

--- Sorry after rereading your post I realized that the separated systems might not be SQL Servers... ---