Ms SQL View

[AdamField]

Hey Guys,

i got a stupid problem with making a SQL View and becouse i have zero sql skills i'm trying here

what i have is in total 5 DB's
all the DB's have 10 colum's with info
now i need to make 1 BIG view from those 5 db's but i need to get 11 colums now
first 10 are the same as what is original in the DB and the 11e is the name of the DB (or DB1 -> DB5)
it doesn't have to do anything special .

as a small example

DB1 Calles ANT

Colum1    Colum2    Colum3    Colum4
 info1     info2     info3     info4
 info4     info5     info6     info7

DB1 Calles EDE

Colum1    Colum2    Colum3    Colum4
 info8     info9     info10    info11
 info12    info13    info14    info15

In the end i should get with a view

View DB

Colum1    Colum2    Colum3    Colum4      Colum5
 info1     info2     info3     info4       ANT
 info4     info5     info6     info7       ANT
 info8     info9     info10    info11      EDE
 info12    info13    info14    info15      EDE

Hope somebody can help me with this

Tnx already

Adam

 

[r937]

SELECT Colum1,Colum2,Colum3,Colum4, 'ANT' AS Colum5
  FROM ANT
UNION ALL
SELECT Colum1,Colum2,Colum3,Colum4, 'EDE' AS Colum5
  FROM EDE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[AdamField]

Tnx Big time r937

Tested it out and it's working perfect,
What would i need to do if i want to ad a 3e DB ?

SELECT *, 'EDE' AS Filiaal
FROM DBFact_EDE.dbo.hisarel
UNION ALL
SELECT *, 'ANT' AS Filiaal
FROM DBFact_ANT.dbo.hisarel
UNION ALL
SELECT *, 'GEN' AS Filiaal
From DBFact_GEN.dbo.hisarel

in total i have 5 of them so not sure if i have to use the union all every time in between.


Adam

 

[r937]

what happened when you tested it? ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[AdamField]

it's working with the union all

and working like a charm

Adam

 

[gmmastros]

I would encourage you to list out all the columns instead of using *. You see... if someone modifies one of the tables, then you view will stop working. By listing the columns, you will not be affected by someone adding a column to the base table. Of course, you will still have a problem if someone removes one the columns, but that is to be expected (and probably less likely to occur).

Ex:

-- Create some test tables
Create Table T1(Col1 Int, col2 Int)
Create Table T2(Col1 Int, col2 Int)
Create Table T3(Col1 Int, col2 Int)

go
-- Create a view to union them all
Create View ALLData
As
Select *, 'T1' As SourceTable
From   T1
Union All
Select *, 'T2'
From   T2
Union All
Select *, 'T3'
From   T3
go
-- Select from the view (no errors)
Select * From AllData

-- Add a column to one of the base tables
Alter Table T2 Add Col3 Int


-- Select from the view (Now you get an error)
Select * From AllData

[tt][red]
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'AllData' because of binding errors.[/red][/tt]

By taking a little more time now to list the columns, you may prevent unwanted errors later.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

yeah, but george, they are identical tables -- just in separate databases

if somebody changes one of them without changing the others, then the error produced as a result of using the dreaded, evil "select star" would be a good thing ™

this is one of those rare situations where using SELECT * isn't all that bad



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[AdamField]

Hey Guys,

Like r937 said, i get this info from our account program (seperated program) but becouse that thing can't do 1/10 of the stuff we want it to do (it's flat file DBF files ) we export to SQL on a daily base
i can be 100% sure that all the DB's are the same.

Still tnx for the example as i got another table where i was planning on using this querry and now i know i can't use * there

Greetings

Adam