SQL query select * from multiple views

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

SQL query select * from multiple views

Author

Topic

bhdijkstra
Starting Member

24 Posts

Posted - 2011-03-02 : 10:29:27

How to make a SQL query to select * from multiple views?

Select * From "database".dbo.view1 is ok, but what so on?

rmtweb
Starting Member

1 Post

Posted - 2011-03-02 : 10:39:38

The easiest way to to UNION ALL (provided that your individual column data-types are the same across views)

e.g.

SELECT col1, col2, col3, col4 FROM dbo.View1
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View2
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View3

You can then query across all views (if you need to):

SELECT * FROM
(
SELECT col1, col2, col3, col4 FROM dbo.View1
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View2
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View3
) As TempTable
WHERE TempTable.col1='YourValue'

Richard Taylor
Chief Software Architect
ISArc Limited

bhdijkstra
Starting Member

24 Posts

Posted - 2011-03-02 : 10:49:55

unfortenely this is not working.

what i need is something like
:

select * from [100.dbo.view], [101.dbo.view], [102.dbo.view]

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-02 : 11:26:43

No, you don't want SELECT *

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bhdijkstra
Starting Member

24 Posts

Posted - 2011-03-03 : 04:28:59

still not working, where to put in the databsenummber in the 'solution':

SELECT col1, col2, col3, col4 FROM dbo.View1
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View2
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View3

You can then query across all views (if you need to):

SELECT * FROM
(
SELECT col1, col2, col3, col4 FROM dbo.View1
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View2
UNION ALL
SELECT col1, col2, col3, col4 FROM dbo.View3
) As TempTable
WHERE TempTable.col1='YourValue'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 04:32:30

use the 3 part name


SELECT  col1, col2
FROM    [100].[dbo].[view]

UNION ALL

SELECT  col1, col2
FROM    [101].[dbo].[view]

UNION ALL

SELECT  col1, col2
FROM    [102].[dbo].[view]

KH
[spoiler]Time is always against us[/spoiler]

bhdijkstra
Starting Member

24 Posts

Posted - 2011-03-03 : 06:27:14

thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources