2 different databases in a SQL Stored Procedure

[vbjohn]

I was wondering if there is a way that we could call 2 different databases in a SQL Stored Procedure?

We have 2 main databases with the same tables. So I am creating a Stored Procedure and would like to call them both and gain access to their tables.


SELECT * FROM CMSN.dbo.PM30600
UNION ALL
SELECT * FROM EMP.dbo.PM30600


Is there a way I can do this?

John-

 

[sunila7]

Hi,

Query as u have typed shd work, u just need to be careful with no of columns returned from the union stmnt... both stmnnt shd return ame no of fields

Sunil

 

[SQLSister]

it is always best to specify the column names in a union query. This way you are sure the mapping will remain stable even if someone adds a new field or rearranges the column order to one of the tables. Union queries must always have the same number of columns and the data types must match up (either the same type, an implicit conversion or and specifed explicit conversion) or you will get an error.
Using the All keyword will get you duplicates, is that what you wanted?