This is not really a SQL-specific issue; the answer depends on your particular database vendor. For example, with Oracle, you would create a DATABASE LINK in database "B" like this:
CREATE DATABASE LINK "A" CONNECT TO CURRENT_USER USING 'DB_A';Then, from database "B," you would simply refer to a table in database "A" like this:
SELECT * FROM MyTable@A;In SQL Server, you would access a remote table this way:
SELECT * FROM [Server].[Database].[Owner].[Table];This is definitely a place where I recommend reviewing your vendor specific documentation.
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2002