How to Insert Data on ServerA that comes from Select Data on ServerB?

Question

Scenario:

I have a development SERVERA which I need to insert data from my tableA from ServerB TableA.

SERVERA.TableA:serialnumber = empty,partnumber=empty

Ex:

Database connection string1.

Select serialnumber,partnumber from ServerB.TableA where (serialnumber='1')

Then

Database connection string2

Insert into serialnumber,partnumber from SERVERA.TableA 

Result:

SERVERA.TableA:serialnumber = 1,partnumber=2

Answer 1

If you're using Sql Server, you can use Linked Servers to provide the facility you need, you'd then be able to execute the following code on ServerB:

INSERT INTO [dbo].[TableA]
(
 SerialNumber,
 PartNumber
)
SELECT SerialNumber,
       PartNumber
FROM [ServerA].[DatabaseNameOnServerA].[dbo].[TableA]
WHERE SerialNumber = '1'

If you can't use linked servers, or want/need to do it in code then that's also fairly simple:

using (var connection = new SqlConnection(connectionStringForServerA)
{
  connection.Open();
  var query = "SELECT SerialNumber, PartNumber FROM [dbo].[TableA] WHERE SerialNumber = '1'";
  using (var adapter = new SqlDataAdapter(query, connection);
  {
    var data = new DataTable();
    adapter.Fill(data);

    using (var connectionB = new SqlConnection(connectionStringForServerB)
    {
       var query = "INSERT INTO [dbo].[TableA] (SerialNumber, PartNumber) VALUES (@Serial, @Part)";
       foreach(DataRow recordFromServerA in data.Rows)
       {
         using(var command = new SqlCommand(query, connectionB)
         {
           command.Parameters.AddWithValue("@Serial", recordFromServerA["SerialNumber"]);
           command.Parameters.AddWithValue("@Part", recordFromServerA["PartNumber"]);

           command.ExecuteNonQuery();
         }
       }
    }
  }
}

There are more efficient ways to carry out the insertion, but having the code spell out exactly what it's doing as I've done there should make it quite clear to you how it works (It is worth noting that I haven't actually run the code sample I've given, so it may need a bit of tweaking, it's also Sql Server specific).