Tables from different Databases

[rbrescia]

How dow I create a view that merges two tables from different databases.

I have two tables with the same name in seperate data bases. I would like to create a view or a mereged table in one of the databases that contains the data is both.

 

[tlbroadbent]

Create the View in one database referencing the fully qualified name (dbname.owner.tablename) of the table in the other database. For sake of documentation, I would fully qualify both table names.

If by "merge" you mean JOIN, then use the following syntax.

Select a.col1 As col1A, a.col2 As col2A, a.col3 As col3A, ..., b.col1 As col1B, b.col2 As col2B, b.col3 As col3B, ...
From dbname1.owner.table1 a Inner Join dbname2.owner.table1
On a.col1=b.col1 And ...

NOTE: Tables are aliased to avoid ambiguous references in the query. Columns are aliased to avoid duplication of column names in the output.

If by "merge" you mean UNION, then use the following syntax.

Select col1, col2, col3, ...
From dbname1.owner.table1
UNION
Select col1, col2, col3, ...
From dbname2.owner.table1 Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot