2

How can I tranfer data between different DB Servers, this is a daily job, i.e:

Insert into ServerA..table1
   select data from ServerB.table2.

(this is just an example, the real situation is we select data from many servers, and then do some join, then insert into the destination).

We can not use SSIS, we can not use linked server, How can we do this?

btw, this is a daily job, and the data is huge.

Guoliang
  • 885
  • 2
  • 12
  • 20
  • I guess you are looking for this answer on SO: [What is the best way to auto-generate INSERT statements for a SQL Server table?](http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table) – Yaroslav Sep 14 '12 at 09:45
  • Hi Yaroslav, many tanks for your reply, my point is, how can we transfer data between different DB Servers without SSIS or linked server. thanks again. – Guoliang Sep 14 '12 at 09:54
  • I got it, you want to do it `on the fly` just from code – Yaroslav Sep 14 '12 at 09:56

5 Answers5

3

A simple command line BCP script should work for you. For instance:

bcp AdventureWorks2012.Sales.Currency out Currency.dat -T -c -SServer1
bcp AdventureWorks2012.Sales.Currency in Currency.dat -T -c -SServer2

Here's more details

Sylvia
  • 2,578
  • 9
  • 30
  • 37
  • thanks for your reply. but I think this is a good solution for my situation. I need run sql from serverA on ServerB.
    and this two SQLs using bcp are run on ServerA and then on ServerB, right?     – Guoliang Sep 16 '12 at 02:53
  • Hi David. The BCP utility can be run from any computer that has network access to the servers, or from the servers themselves. Please don't forget to mark this as the answer if it works for you. – Sylvia Sep 17 '12 at 04:34
1

The Sync Framework might be worth a look : http://msdn.microsoft.com/en-us/sync/bb736753.aspx

Rikalous
  • 4,514
  • 1
  • 40
  • 52
0

Look at this question: SQL backup version is incompatible with this server

The first options from my answer should work for your case

Community
  • 1
  • 1
Jester
  • 3,069
  • 5
  • 30
  • 44
0

My answer was converted into comment but I'm adding some more info.

I guess you are looking for this answer on SO:

What is the best way to auto-generate INSERT statements for a SQL Server table?

Once you have the code, just add USE your_databasename_where_to_copy_data at the begining, execute and voila

Edit: As you want to do it on the fly, using code, try some of the solutions provided on this question on SO. Basically it is similar to your code proposal, with some few differences, as for example:

INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar
Community
  • 1
  • 1
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
0

You can use C#.net SqlBulkCopy method.

AnandPhadke
  • 13,160
  • 5
  • 26
  • 33