[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select data from different servers in MS SQL

Posted on 2006-10-31
11
Medium Priority
?
218 Views
Last Modified: 2008-02-01
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
Comment
Question by:woo_kh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Accepted Solution

by:
HuyBD earned 180 total points
ID: 17847275
first, create link server then user OPENQUERY to query

select * from openquery(LinkedServer, 'Select * from England.London.Sample')
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17847287
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17847332
u can use this query

SELECT *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=server-name;User ID=id;Password=pwd'
                   ).DBNAme.dbo.TableNAme
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17847345

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

Expert Comment

by:regbes
ID: 17847940
Hi woo_kh,

if you have added eact server as linked servers

Select * from England.London..Sample

HTH

R.
0
 
LVL 11

Expert Comment

by:regbes
ID: 17849580
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
 

Author Comment

by:woo_kh
ID: 17854839
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17855416

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
 

Author Comment

by:woo_kh
ID: 17855532
Yes, with values!

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

Any idea?
0
 
LVL 11

Expert Comment

by:regbes
ID: 17855905
in EM use the copy database wizard
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17857956
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question