0

Is it possible to Union 6 Columns with different tables different database and different server?

Please tell me if this is possible or not?

rae1
  • 6,066
  • 4
  • 27
  • 48
Kristian Hernan C. Manuel
  • 95
  • 3
  • 17

2 Answers2

1

If you're looking to query across multiple databases on different servers using SQL Server, you should look at linked servers >> http://msdn.microsoft.com/en-us/library/ms188279.aspx

Once you've added all the linked servers you need to access, the tables on those servers can be accessed as if they were "local". Then you just need to worry about performance.

mockaroodev
  • 2,031
  • 1
  • 20
  • 24
  • Thanks @skydump can you give an example ? – Kristian Hernan C. Manuel Dec 21 '12 at 03:56
  • 1
    I don't have one off hand as I'm on a mac right now - but it's super easy to set up if you're using Management Studio. Just find linked servers in the database browser on the left side and add one. The wizard makes it pretty straightforward. Look at this post to see how you would access tables in linked server via sql >> http://stackoverflow.com/questions/4091960/sql-server-linked-server-example-query – mockaroodev Dec 21 '12 at 04:00
  • Thanks @skydump so how about with the 6 columns with different tables and databases? – Kristian Hernan C. Manuel Dec 21 '12 at 04:11
1

AS previous answer you need linked servers for a start.

When the servers have been added to your server where you are executing the query from you can run the query as follows

SELECT Field1 , field2
FROM   LINKED_SERVER1.DatabaseName.dbo.tableName_X

UNION ALL

SELECT Field1 , field2
FROM   LINKED_SERVER2.DatabaseName.dbo.tableName_Y

You can add as many tables to the union as you like, just following basic UNION rules. I.e all of the selects in the union must have the same number of fields, and of compatible datatypes

Richard Vivian
  • 1,700
  • 1
  • 14
  • 19
  • Sorry for the misunderstanding what i want is the inner join can you know how to implement this in UNION it appears only one column..this is what i want to do. http://stackoverflow.com/questions/13986667/selecting-specific-columns-from-different-tables-and-different-database/13986767#comment19303560_13986767 – Kristian Hernan C. Manuel Dec 26 '12 at 02:12
  • Hi Kristian, Does this mean that you have your solution. As noted in the other question on SO, yes you can JOIN on tables across linked servers. There are some performance tricks when working with linked servers. I tend to select the remove data into a memory table (DECLARE @TEMP AS TABLE), copy remote server data into the table, then JOIN to the local server. This often performs much better than a straight join on a linked server. – Richard Vivian Dec 26 '12 at 08:41