Alex Rivera | Logout

TSQL: Create a view that accesses multiple databases

Asked 2010-01-26T22:34:31.093
43

I have a special case,

for example in table ta in database A, it stores all the products I buy

table ta(
id,
name,
price
)

in table tb in database B, it contain all the product that people can buy

table tb(
id,
name,
price
....
)

Can I create a view in database A to list all the products that I haven`t bought?

sql-server database t-sql view
Edit
Report

1 Answer

7

Yes, views can reference three part named objects:

create view A.dbo.viewname as
select ... from A.dbo.ta as ta
join B.dbo.tb as tb on ta.id = tb.id
where ...

There will be problems down the road with cross db queries because of backup/restore consistency, referential integrity problems and possibly mirorring failover, but those problems are inherent in having the data split across dbs.

answered 2010-01-26T22:43:27.460

Your Answer