Cross Database Query

[Waynest]

In Query Analyser, is it possible to write a query which reads out of a table in database 'a' and writes into another table in database 'b' on the same server? Can someone give me an example of the syntax if this is possible?

 

[rajeevnandanmishra]

Hi Waynest,
Using any database other than MASTER and PUBS run the following query and see the result in PUBS database.

select * into pubs.dbo.mynewTable from master.dbo.sysdatabases

 

[karluk]

Yes, if you have the appropriate permissions. Just include the database name in the fully qualified table name. For example

insert into database_b.owner_b.table_b
select col1, col2, ...
from database_a.owner_a.table_a