OpenRowSet all DB's

[moone009]

I'm running sql server 2005 and I need to pull information from 2 of the databases(all from the same server). All of the databases are identical minus the data.

database1 = LCDEL
database2 = LCDFV

I need all of the location data from each database and instead of querying them one by one. I would like to use the openrowset function but I cannot seem to figure it out.

Because then I could toss the data into a temp table and run my analysis instead of using excel.

[adam_k]

You should not have any problem as long as you specify the table.owner in your select:

select * from LCDEL.dbo.tablename
select * from LCDFV.dbo.tablename

You don't need an openrowset to read from SQL Server. Not even if they weren't in the same server (you would need to link the 2 servers though).

You can join them or union or whatever you would do as if they where 2 tables of the same db.

[ChrisPadgham]

Investigate a UNION query if you want the data from both tables at once

[adam_k]

Investigate a UNION query if you want the data from both tables at once

UNION will just give results from both tables in 1 result set. Depending on the job to be done and the available fields, this might not help when comparing data, besides if you don't use UNION ALL you won't get the duplicates results if any.

In any case, if you prefer to use UNION at least add a field by giving a value in each select, so that you can tell which data originated from which table.
eg.

select 'table1',* from table1
union select 'table2',* from table2