Dump data from one database table to the other database

I have to write a code in vb.net (2010). To get a data from Database A table 1 and dump it to Database B Table 2. What’s the best way to do this? Most efficient way? I know I can do this very easily in sql management studio but I need to write a code to do th开发者_JAVA技巧is in vb.net. Thanks!

For MSSQL (which I believe you use if you use .NET) the best and fastest way is to use bulk inserts.

You can find the documentation here: http://msdn.microsoft.com/en-us/library/ms188365.aspx

You could begin by writing a SQL query to do it in Management Studio, and then get that to run from VB.Net

Your SQL will look something like:

SELECT * INTO DatabaseB.dbo.Table2 FROM DatabaseA.dbo.Table1

To run this in VB.Net, you should look at System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand. You first make the SqlConnection with your database details, then you make the SqlCommand, passing in the SqlConnection and the SQL you want to run.

Don't forget to dispose the SqlConnection when you are done (preferably put it in a using block).