query to copy and work with data on different server

Question

Post reply

query to copy and work with data on different server

scottichrosaviakosmos

SSCommitted

Points: 1889
More actions
July 26, 2010 at 12:14 am

#221650

i want to copy data from one database to another on different machine or server.
and i also want to perform differnt query operation on that other server database and its tables.
i know tat there is something called link server but i dont know to configure it and wt to do in that.
can someone tell me steps for all this i m new to this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 1

To copy data... There r several ways in sql... check BOL for the same...

for Linked Server.. Well explained in BOL...

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

BCP is faster than linked server...

1. First BCP out data into csv file from Server1

2. copy the csv file into server2

3. Then BCP in the same file into Server 2..

This will save u lot of things, ur network bandwidth , ur processing time, ur table will be free once the BCP completes..

vijay.s SSCrazy Points: 2161 More actions · Answer 3

BCP is only used for bulk loading the database...

For other operation you need, link server connectivity. A simple wizard is available for creating the same.

For reference

http://msdn.microsoft.com/en-us/library/aa560998%28BTS.70%29.aspx

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

vijay.s (7/26/2010)
BCP is only used for bulk loading the database...[/url]

Vijay, are u sure apart from "Loading", nothing can be done through BCP ? 🙂

There is a switch called "queryout" in BCP...Please look into that..

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

If the task you are implementing going to be regulary executed, I would suggest using SSIS, as it is specifically designed to do so.

BCP will be the fasterst method to extract large chunk of data from one Server/DB and load it to another, however you will have limited transformation ability...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

MMartin1 One Orange Chip Points: 27795 More actions · Answer 6

If you want to transfer data from server 1 to server 2, you can create a linked server connection object on server 2 that point back to 1. From there simply right click the database you want to import into, select tasks, import data and follow the wizard. It is relatively straightforward.

----------------------------------------------------