• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Select data from different servers in MS SQL

I have:

Server1:  England          Database:   London       Table: Sample
Server2:  Germany         Database:   Berlin         Table: Sample

1) I wish to select data from Server1, table Sample.....Should I write' Select * from England.London.Sample'??

2) I wish to copy The table 'Sample' from Server 1 to Server2.  Please advise?

Please advise.

Thank you very much.
0
woo_kh
Asked:
woo_kh
  • 4
  • 3
  • 2
  • +1
1 Solution
 
HuyBDCommented:
first, create link server then user OPENQUERY to query

select * from openquery(LinkedServer, 'Select * from England.London.Sample')
0
 
HuyBDCommented:
if you are in server 2, create a link server to server1,

insert into Sample select * from openquery(LinkedServer, 'Select * from England.London.Sample')
0
 
Gautham JanardhanCommented:
u can use this query

SELECT *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=server-name;User ID=id;Password=pwd'
                   ).DBNAme.dbo.TableNAme
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Gautham JanardhanCommented:

insert into sample SELECT *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=server-name;User ID=id;Password=pwd'
                   ).DBNAme.dbo.TableNAme
0
 
regbesCommented:
Hi woo_kh,

if you have added eact server as linked servers

Select * from England.London..Sample

HTH

R.
0
 
regbesCommented:
woo_kh,
> 2) I wish to copy The table 'Sample' from Server 1 to Server2.  Please advise?
 you can use DTS or SSIS for this
0
 
woo_khAuthor Commented:
Dear all,

When I have a look again on the SQL server, what I have is not 'linked server'.

When I clicked on the SQL Server Group, there are 2 servers, one is called 'local' and another is called 'database'.  Both servers have databases such as 'Northwind','Pub',etc.  The only difference is that the 'local' server has a database called 'sample' but not in the 'database' server.

I would like to copy the database 'sample' from server 'local' to 'database' using T-SQL? Any idea?

Please advise.  Please accept my apologise for the wrong questions.

Thank you.

Cheers,
0
 
Gautham JanardhanCommented:

if u just wanna copy the database without values

1) u can generate script of ur sample db form ur local using EM and run this script in ur other server

or else

u can use 3 rd party tool red gate tools

for comparing and synching data
0
 
woo_khAuthor Commented:
Yes, with values!

I need to copy all the databases from server 'Database' to server 'local'.

Any idea?
0
 
regbesCommented:
in EM use the copy database wizard
0
 
Gautham JanardhanCommented:
1) u can use copy database wizard in enterprise manager as regbes suggested.


right click the source server in EM

ALL Tasks --> Copy database wizard
and follow the steps in the wizard.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now