I have to programmatically (ADO.Internet) copy records from the table in a single database to some table in another database on the different server.
This is much like "How can I copy data records between two instances of an SQLServer database" except that i'm not permitted to produce a connect to the destination server therefore the recognized response to that question will not work with me.

The SqlBulkCopy class recommended by santiiii is extremely efficient however it produces a non-drenched operation. I needed to do that once but my target database took part in replication, and so i needed the operation to become fully drenched. Things I basically wound up doing was choosing a dataset in the source database .

Select * from SourceDatabaseTable where (some clause to get the right records)

Then creating a clear dataset in the destination table with this particular statement

Select * from DestinationDatabaseTable where 1<>1

I Quickly had two datasets. The very first using the records I needed copying and also the second that's empty. Next I simply did a nested foreach loop copying the records in one dataset towards the other. This is actually the Pseudocode for that core copy function: