SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating views on multiple tables


Creating views on multiple tables

Author
Message
bilalhazrvi57
bilalhazrvi57
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 17
Hi,
Is it possible to create views using multiple tables from multiple databases????
I have 4 servers which are connected remotely and the same database resides on all the servers, I want to create view by using all the databases of all the servers??
I want to do this to make the database available for the user regardless of the connection that they connect to which server?? means data must be available to users either they connect to one server or other????
your valuable guidelines and suggestions are welcome.
Thanks in advance
BILAL AHMAD
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49485 Visits: 10844
Assuming the tables you want to expose in a single view are all the same, have you tried something like this?

CREATE VIEW BillsView
AS

SELECT field_list
FROM [server,port].database_name.dbo.table_name
UNION
SELECT field_list
FROM [server2,port2].database_name2.dbo.table_name;




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 798
hi,

in SQL you can reference to each object using a 4-part name: [Server].[Database].[Schema].[Table]
[Server] has to be registered as a Linked Server, for details have a look at books online (http://msdn.microsoft.com/en-us/library/ms190479.aspx)

Depending on the amount of data the table on the other servers contain and the kind of view you might create (using joins over several servers) the peformance might be very poor.
Depending on what you actually have on data and what you actually need there might be other solutions than views...
bilalhazrvi57
bilalhazrvi57
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 17
Hi Bills,
Thanks a lot for your kind suggestions. Look you told me to do this, that one table from server1 union with one table from server 2. It works quite good, my I am looking for this,
that more than one table from server 1 and more than one table from server 2, is it possible?????

create view gvWDatabase
as
select * from Accounts
union
select * from Account_benefits
union
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits

Look here the first two tables Accounts and Accounts_benefits are from the server to which currently I am logging and the next two lines
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits
that two tables Accounts and Account_benefits are from the remote server Lahore which is registered as remote server.
I want to add more than one table from one server and more than one table from 2nd server, is it possible???
Please reply?
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6054 Visits: 5280
bilalhazrvi57 (5/30/2013)
Hi Bills,
Thanks a lot for your kind suggestions. Look you told me to do this, that one table from server1 union with one table from server 2. It works quite good, my I am looking for this,
that more than one table from server 1 and more than one table from server 2, is it possible?????

create view gvWDatabase
as
select * from Accounts
union
select * from Account_benefits
union
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits

Look here the first two tables Accounts and Accounts_benefits are from the server to which currently I am logging and the next two lines
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits
that two tables Accounts and Account_benefits are from the remote server Lahore which is registered as remote server.
I want to add more than one table from one server and more than one table from 2nd server, is it possible???
Please reply?


If it works for one table from another server, it should definitely work for more than one tables as well :-)


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
bilalhazrvi57
bilalhazrvi57
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 17
hi,
yes it works for one tables means it can access data from remote server and combine with currently logged server's table, but when i want to add two tables in view of current server and of remote server than it gives the error
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6054 Visits: 5280
bilalhazrvi57 (5/31/2013)
hi,
yes it works for one tables means it can access data from remote server and combine with currently logged server's table, but when i want to add two tables in view of current server and of remote server than it gives the error


What is the error you are receiving?


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
bilalhazrvi57
bilalhazrvi57
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 17
That is the error which i receives
"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
"
eswar.l
eswar.l
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 81
That error is correct only. You have to check whether there are equal number of columns in each of those tables. You can otherwise create CTE and post all the records in them.
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6054 Visits: 5280
bilalhazrvi57 (5/31/2013)
That is the error which i receives
"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
"


Please check the number of columns in your SELECT's
It should be equal in all your queries that you are trying to combine in your View.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search