[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select Statement between different servers?

Posted on 2011-05-09
8
Medium Priority
?
446 Views
Last Modified: 2012-05-11
Hello Guys,
Is there anyway I can select data from a database that is in server 1 and insert it into a database in server 2 ?

Thanks
0
Comment
Question by:ankouny
8 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35726135
Export the data in Excel file
tehn import in new database
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 35726136
u can use linked server option
0
 
LVL 18

Expert Comment

by:dj_alik
ID: 35726141
SQL Server has a few mechanisms to reach out to another server (even another server type) and query data from within a Transact-SQL statement. Among them are a set of stored credentials and information (called a Linked Server), a statement that uses a linked server called called OPENQUERY, another called OPENROWSET, and one called OPENDATASOURCE.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 35726142
Yes, you can do it via OPENDATASOURCE like the one shown below:
Execute the below query from SERVER1 to fetch data from SERVER2

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=SERVER2;Integrated Security=SSPI')
    .database_name.schema_name.table_name

Similar way, you can insert records into SERVER2 from SERVER1 using the query below:

INSERT INTO schema_name.table_name ( col1, col2, col3)
SELECT col1, col2, col3
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=SERVER1;Integrated Security=SSPI')
    .database_name.schema_name.table_name
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 35726148
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35726163
ankouny,

If you want to fetch the data from another server in a ad-hoc manner or one time, then you can go with OPENDATASOURCE query provided in my earlier comment.
If you want to do this is in a regular manner and periodically, then you can create a Linked Server ( provided you have appropriate privileges) and fetch records appropriately.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35727424
Yes, you can do it, if the servers are linked. See screen shot for more help  image
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35727964
PatelAlpesh,

>> Yes, you can do it, if the servers are linked.

You can also do it without creating a Linked Server using OPENDATASOURCE as I mentioned above..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

830 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