How to retrieve a database on the other servers

Question

I want to retrieve a table on a SQL Server database that is located on another server and I want to store the data retrieved into my own SQL Server database.

How do I can do that?

Thanks so much

Answer 1

one of the easiest and best method i found in this article this might be resolve your issue easily

SQL SERVER – 2008 – Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table

Answer 2

One of the fastest method if you have a lot of data is a tool called bcp. It allows you to export and import data to a file. So you can export from the source database and then import to the target. It is very fast.

Answer 3

If your destination is a SQL 2008 database and you're set on using C# to connect to the source and get the data you could use a Table parameter. A DataTable in .NET is directly mappable to a User Defined Table type in SQL Server.

Here is a SO thread about it:

How to pass User Defined Table Type as Stored Procedured parameter in C#

Define your custom table type in your destination database

 create type MyCustomTable as Table 
 ( 
     Field1 int,
     Field2 varchar(50), 
     Field3 decimal(18,0) 
 ) 

The concept would be to read all of the data from the source in to a data table. Then you would use a SqlParameter to execute a stored procedure or possibly text query on your destination server. By using a stored procedure that accepts a table parameter you could do the following:

 CREATE PROCEDURE dbo.BulkCopyData
 (
      @SourceData MyCustomTable readonly --readonly has to be there, Table params have to be readonly as parameters

 ) AS
 BEGIN

      INSERT INTO dbo.DestinationTable
      (
           Field1,
           Field2,
           Field3
           --more fields
      )
      SELECT Field1,Field2,Field3 FROM @SourceData


 END

And in C# when you go to execute the command:

DataTable dt = new DataTable(); //Go get the data from your source here


 SqlConnection conn = new SqlConnection("....");
 conn.Open();
 SqlCommand cmd = new SqlCommand("dbo.BulkCopyData",conn)
 cmd.Parameters.Add( new SqlParameter("SourceData", SqlDbType.Structured){ TypeName = "dbo.MyCustomTable ", Value = dt});
 cmd.Parameters[0].
 cmd.ExecuteNonQuery();

Answer 4

You can use also openrowset function on SQL and call/query the remote server using your SQL code. This feature is not enabled by defauld (you must use the SP_CONFIGURE stored procedure and enable the remote queries to use this functionality). Here is a link with some examples.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

When you need to know how to set up the configuration just let me know ;)

Answer 5

Connect to your DB using SQL Server management Studio

Go to Server Objects ->Add a new Linked Server

then you can use the other table as select * from LinkedServerName.DBName.dbo.TableName