How to do a union across multiple Sql Server?

Question

I have two Sql Servers (two distinct databases, (i.e. two machines)) with the same structure. Is there a way to do a SELECT * FROM (SELECT * FROM TableOnServerA Union SELECT * FROM TableOnServerB)?

Thanks

Answer 1

SELECT * FROM serverA.database.owner.TableName
Union 
SELECT * FROM serverB.database.owner.Tablename

assuming that they are setup as linked server, use books online and go to "linked"

Answer 2

Yes, just set them up as linked servers and then fully qualify the names in the form of LinkName.DatabaseName.SchemaName(dbo).TableName

Answer 3

You could create a Linked Server or use OPENROWSET to connect to other SQL database.

Answer 4

You can run a query relating two different machines adding one machine via the sp_addlinkedserver stored procedure. You run it in the database server instance where you will want to execute the query (or on both if you want to execute the query in any server), like this

use master
go
exec sp_addlinkedserver 
@server='AnotherServer',
@provider='SQL Server'

The you can run

select * from AnotherServer.database.dbo.table t1 join database.dbo.table t2 on (t1.id = t2.id)

Answer 5

Incidentally if they are on two different servers and you are sure the data is not duplicated use union all, it will be much faster. Also never use select *,specify the field names. Other wise it will break if someone adds a column to A but not to B (or rearranges teh columns in B but not A) I would also add a column indicating which of the two servers the data came from, especially if they might have the same id number but attached to different data (which can happen if you are using autogenerated ids). This can save no end of trouble.