CODEWITHSUNDEEP
sql-server
Tony
Tony

Reputation: 3409

stored procedure for different databases

Can I use a stored procedure to select records from different databases (in SQL)? I have a table from each database and I want to join them.

Thanks.

Upvotes: 1

Views: 8322

Answers (5)

Abe Miessler
Abe Miessler

Reputation: 85056

Yes, as long as you have permissions. You can also do cross server if you have a Linked Server setup.

Cross DB Example:

SELECT * FROM localTable as lt
INNER JOIN otherDB.dbo.OtherTable as ot
ON lt.ID = ot.id

Cross Server example (assumes you have created the linked server):

SELECT * FROM localTable as lt
INNER JOIN ServerName.otherDB.dbo.OtherTable as ot
ON lt.ID = ot.id

Upvotes: 5

Rozwel
Rozwel

Reputation: 2020

One other technique that works better in some environments is to define a synonym for the remote table. This lets you have a local reference that points to the remote object. Particularly useful if you are in an environment where you have to migrate things and the relative locations may not be the same from one environment to the next. Here is a quick overview:

http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/

Upvotes: 0

Ian
Ian

Reputation: 879

Try something like the following:

SELECT a.userID, b.usersFirstName 
FROM databaseA.dbo.TableA a 
INNER JOIN database B.dbo.TableB b ON a.userID = b.userID

Source: http://forums.asp.net/t/1254974.aspx

Upvotes: 1

Hari Menon
Hari Menon

Reputation: 35405

Use the fully qualified table name. e.g, If you are in database ABC and want to access table table in database XYZ, access it as XYZ.dbo.table (It could be dbo or something else depending on the owner of the table. Usually it is dbo)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453287

Yes. Just use 3 part naming syntax database_name.schema_name.table_name.

You also need to either enable cross database ownership chaining. (Not recommended) or use this approach.

Upvotes: 3

Related Questions