join tables on different server

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

General SQL Server Forums

New to SQL Server Programming

join tables on different server

Author

Topic

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-06 : 13:24:00

HI guys,

Again, I am stuck in joining 7 tables into one.
I have 7 tables on server A that I need to pick few common columns and put it on server B in one table with additional column which will tell the table name for which data belongs to.
Like if I have 7 tables on server A with names tbl1, tbl2, ... tbl7 then I need tbl8 on server B with common columns selected say col1, col2, col3 in all tables + the table name for which it belongs to.

so, result set would appear like

col1 col2 col3 tbl_name
2 abc 3 tbl3
5 xyz 4 tbl1 and so on.

Many thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-06 : 13:26:18

you need to use openrowset or add second server as a linked server to first.then use query like


SELECT col1,col2,col3,'tbl1'
FROM tbl1
UNION ALL
SELECT col1,col2,col3,'tbl2'
FROM tbl2
....
SELECT col1,col2,col3,'tbl8'
FROM [ServerB Name here].[DBname].dbo.tbl8

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources