select from two servers. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select from two servers.

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-21 : 16:00:04

Hello,

Is there way to list all records in one select query if we have more than 2 sql server but same table structures?

for e.g. i have data 5 records on server1's database (Id,FName) and 10 records in server2's database (Id,FName), table name is same tbl1.

so when we run select statement script, it will pull all 15 records.

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:03:45

its possible provided you set up a linked server connection from one server to the other. then it would be as simple as


USE [DB1]
GO
SELECT * FROM dbo.tbl1
UNION ALL
SELECT * FROM [Server2].[DB2].[dbo].tbl1

if its for an adhoc access you can even use OPENROWSET but you've have adhoc distributed queries option enabled in your server for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-21 : 16:17:37

okay, how do we use where condition, if server1.db1.fname='aaa' and server2.db1.fname='bbb' like that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:19:27

just like you use it in query


USE [DB1]
GO
SELECT * FROM dbo.tbl1
UNION ALL
SELECT * FROM [Server2].[DB2].[dbo].tbl1
WHERE somecol=<some value>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-03-22 : 03:48:07

Just be careful with these cross-server queries, they tend to be resource hogs if you don't know what you're doing. The best tip is to keep the queries that go to the different servers as separated as possible. You should for example try to stay away from joining between servers.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources