How to copy data from a DB to other DB in different server

[RH+]

I have to query one table and insert the result in a table on another database in another server
The query is "select ID, name from Table"
The other table exists and have the same fields.
How can I do this?
thanks!

[dglienna]

Write two queries. Copy from one, then save to the other. Use two connection strings, to different servers.

[RH+]

Write two queries. Copy from one, then save to the other. Use two connection strings, to different servers.

Sorry, That is what I have now :S sorry about not telling that before
I have the 2 connection strings, have the first query.
But dont know what to do with the result of the first query (sorry Im new in VB.NET) in VB6 i think I would be using recordsets...filling the recordset and loop while inserting each value in the seccond DB.
What will be the best approach in .NET?
Thanks again dglienna

[Shuja Ali]

In a similar way, you could open a Datareader and then loop through the datareader and insert 1 record at a time in the second database.

[Eltokhy]

yes the data reader approach will work but to have an optimized solution in your case, I think you have to avoid the data reader in this situation since the source and target are in different servers.

using dataadaptor approch is better in this scenario. (Get the whole data without looping, then close the connection, then paste the data to other server)
what i mean is to minimize the connection opened between both servers while looping or doing any stuff.

Generally the design of ado.net is different than classic ado in terms of continues connectivity to the database.

[dglienna]

yes the data reader approach will work but to have an optimized solution in your case, I think you have to avoid the data reader in this situation since the source and target are in different servers.

The other table exists and have the same fields.

The DR is a good thing.