CODEWITHSUNDEEP
sqlsql-server
Guoliang
Guoliang

Reputation: 895

How to transfer data between different DB servers - No SSIS, No LinkedServer

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.

Upvotes: 2

Views: 1885

Answers (5)

Sylvia
Sylvia

Reputation: 2616

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

Upvotes: 3

Rikalous
Rikalous

Reputation: 4564

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

Upvotes: 1

Yaroslav
Yaroslav

Reputation: 6534

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

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13496

You can use C#.net SqlBulkCopy method.

Upvotes: 0

Jester
Jester

Reputation: 3317

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

The first options from my answer should work for your case

Upvotes: 0

Related Questions