Using C# (vs2005) I need to copy a table from one database to another. Both database engines are MS SQL 2005. For the remote database, the source, I only have execute access to a sproc (stored procedure) to get the data I need to bring locally.

The local database I have more control over as it's used by the [asp.net] application which needs a local copy of this remote table. We would like it local for easier lookup and joins with other tables, etc.

Could you please explain to me an efficient method of copying this data to our local database.

The local table can be created with the same schema as the remote one, if it makes things simpler. The remote table has 9 columns, none of which are identity fields. There are approximately 5400 rows in the remote table, and this number grows by about 200 a year. So not a quickly changing table.

I don't know that this would work without select access on the source database, but it is definitely something to investigate due to its sheer speed compared to other methods!
–
Bryan RehbeinFeb 19 '09 at 15:50

Perhaps SqlBulkCopy; use SqlCommand.ExecuteReader to get the reader that you use in the call to SqlBulkCopy.WriteToServer. This is the same as bulk-insert, so very quick. It should look something like (untested);

Ah, very interesting, I like it. Where am I defining the target command/where the data is going withing the defined connection? Or should I read up on SQLBulkCopy for my answer?
–
BrettskiFeb 19 '09 at 17:17

I would first look at using SQL Server Intergration Services (SSIS, née Data Transfer Services (DTS)).

It is designed for moving/comparing/processing/transforming data between databases, and IIRC allows an arbitrary expression for the source. You would need it installed on your database (shouldn't be a problem, it is part of a default install).

Otherwise a code solution, given the data size (small), pull all the data from the remove system into an internal structure, and then look for rows which don't exist locally to insert.

Yes a very viable option and one I have used often in the past. For this particular project I didn't want the external dependency of an SSIS job. I am curious on how to keep everything in the code on this one. Thank you for your response
–
BrettskiFeb 19 '09 at 17:12

You probably can't do this, but if you can't, DON'T do it with a program. If you have any way of talking to someone who controls the source server, see if they will set up some sort of export of the data. If the data is as small as you say, then xml or csv output would be 100x better than writing something in c# (or any language).

So let's assume they can't export, still, avoid writing a program. You say you have more control over the destination. Can you set up an SSIS package, or setup a linked server? If so, you'll have a much easier time migrating the data.

If you set up at bare minimum the source as a linked server you could write a small t-sql batch to

Doesn’t seem to be huge quantity of data you have to synchronize. Under conditions you described (only SP to access the remote DB and no way to get anything else), you can go for Marc Gravell’s solution.
In the case the data can only grow and existing data can not be changed you can compare the record count on remote and internal DB in order to optimize operation; if no change in remote DB no need to copy.