Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote RDBMS and select them into a local table?

share|improve this question

3 Answers

up vote 5 down vote accepted

Sure, in SQL Server at least you can set up a linked server to other SQL Servers, Oracle, MySQL etc. and pull data from them. There are other approaches as well (such as OPENQUERY). An example would be:

SELECT * INTO dbo.localtable FROM OtherServer.database.dbo.TableName;

In Oracle I think the equivalent is database link but I confess that I have never used it.

share|improve this answer
up vote 3 down vote

In Oracle, you can use a database link object to do it.

CREATE TABLE localtable as (SELECT * FROM remote_table@link_name)

share|improve this answer
up vote 2 down vote

Linked Server & Openquery suggested by @Aaron is simplest and fastest option for sql server to connect to remote server, but just to present the variety, you can also develop a SSIS package for sql 2005 & above or a DTS package for sql 2000, these are ETL tools of SQL server

share|improve this answer
SSIS/DTS seems overkill for a single table here and there. – Aaron Bertrand Jun 13 '12 at 1:37
indeed, i agree with you about this issue, in the OP its mentioned I've been wondering about how multiple databases are automated and to get data from a remote RDBMS , so incase @ugurcode wants to know different ways. – AmmarR Jun 13 '12 at 4:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.