CODEWITHSUNDEEP
sqlsql-server-2008c#-4.0
NoviceMe
NoviceMe

Reputation: 3256

Copy table data from one server to other server

ServerA

Table1
Id  Name    State   Country
1   Abc OH  USA

Table2
Id  Counties    Places
1   abc     def
1   koi     koii
1   joi     joio

ServerB

Table1
Id  Name    State   Country

Table2
Id  Counties    Places

I have 2 servers ServerA and ServerB under both servers there are two tables Table1/Table2 i am trying to copy single rows from Table1 and associated rows from Table2 to ServerB's Table1 and Table2.

This is how i tried doing it:

connection1 = connection to ServerA
connection2 = connection to ServerB

SqlCommand cmd1 = new SqlCommand("Select * from Table1 where id = 1");
SqlCommand cmd2 = new SqlCommand("Select * from Table2 where id = 1");

Connection1.Open();
Connection2.Open();

SqlDataReader reader1 = cmd1.ExecuteReader();
SqlDataReader reader2 = cmd2.ExecuteReader();

var value1 = reader1.read();
var value2 = reader2.read();

i use SqlDataReader and execute above commands and get the data and insert it into ServerB Table1 and Table2. Is this correct way to do this? Can a better sql command be used?

Upvotes: 1

Views: 141

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

Try Using SQLBULKCOPY (See Example here) which is much faster especially if you have huge data

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Most databases allow you to connect to other databases. The particular sytnax is highly database dependent. However, once you do, you can do something like:

select *
into server2.<db2>.<schema2>.table2
from server1.<db>.<schema>.table1

or

create table server2.<db2>.table2 as
    select *
    from server1.<db>.table1

(The syntax depends on the database. The first is sql-server-like the second is oracle-like.)

Using a linked server is definitely the best way to go, if your data has any size. By reading it into memory, you may be tempted to read and insert one row at a time. If you have more than a few dozen rows, you'll find this way more time consuming than necessary.

Upvotes: 0

Related Questions