0

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?

5

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.

3

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

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

2

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

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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