2 server in 1 query

Question

2 server in 1 query

Kaniguan_SQL

Say Hey Kid

Points: 681
More actions
May 31, 2012 at 8:11 pm

#270029

Hi Forumer's
I would like to create an script to pullout data from 2 different server and DB\Table. The first table tableA is stored in server1 and tableB stored in sever2. Is this possible? what are the requirements or option should i do? Kindly please help me guys on how to do this.
SERVERNAME: SERVER1
DBNAME: SAMPLEDB1
TABLE: TABLE1
--------------------
SERVERNAME: SERVER2
DBNAME: SAMPLEDB2
TABLE: TABLE2
Thank yuo in Advance.
jov

Viewing 5 posts - 1 through 4 (of 4 total)

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

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 1

A few options are linked servers, PowerShell (executing desired code across multiple servers), and CMS (Central Management Server). How often do you want to do this and what's the surrounding scenario?

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

Kaniguan_SQL Say Hey Kid Points: 681 More actions · Answer 2

I will be using this on a weekly basis.

I have to copy data from Server1 and replicate or upload data into Server2.

Sample:

Insert into Server2.DBName.Tablename

Select * from Server1.DBbame.TableName

Btw, How do i used the linked server?

do i need to run like this

sp_addlinkedserver [remoteserver Name]

---run the script that pullout records from server1

--and insert records into server2

dogramone Hall of Fame Points: 3216 More actions · Answer 3

OK, so what your are really saying is you have two servers that need their data to be synchronised on a weekly basis. This is not querying and there are many options available. One is to bakup the databases on server one and then restore them on server two. This will work as long as all of the other objects (tables, views, stored procedures, etc) are to remain in synch between the two. Another option is replication, this is just keeping the data in synch. And yet another option is to use SSIS to copy data across servers. And still more options, BCP from one server to teh next.

Plese explain what you are trying to acheive (your requirements) and then you will get a flood of people giving you the solution taht will definately exceed your needs.

Kaniguan_SQL Say Hey Kid Points: 681 More actions · Answer 4

Thanks for giving suggestions and for the reply.

Thank you very much!