Noobie: Need help with a union view

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

SQL Server 2000 Forums

Transact-SQL (2000)

Noobie: Need help with a union view

Author

Topic

kramerd1506
Starting Member

8 Posts

Posted - 2003-06-10 : 13:39:04

Hello all. This is probably pretty basic, but I'm having a hard time.

I have 2 tables (both named property) from 2 different databases whose schemas are identical (although their data is different).

I need to combine the 2 tables into one view, but I need to add a new column (DBFlag) to the view to indicate which database the record came from.

So far I've got a simple union combining select * statements for the two databases, which gives me the record count I need. I just need to figure out how to tell which DB each record came from.

How can I do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-10 : 13:41:06

CREATE VIEW v_SampleView
AS

SELECT Column1, Column2, Column3, 'From DB1'
FROM DB1.dbo.Table1
UNION
SELECT Column1, Column2, Column3, 'From DB2'
FROM DB2.dbo.Table1

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-10 : 13:52:08

Don't forget to give the new column a name!

CREATE VIEW v_SampleView
AS

SELECT Column1, Column2, Column3, 'From DB1' as FromDatabase
FROM DB1.dbo.Table1
UNION
SELECT Column1, Column2, Column3, 'From DB2'
FROM DB2.dbo.Table1

- Jeff

Edited by - jsmith8858 on 06/10/2003 13:52:23

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-10 : 14:00:10

quote:

'From DB2'

I thought this was for SQL Server...

Brett

8-)

kramerd1506
Starting Member

8 Posts

Posted - 2003-06-10 : 14:16:59

There is no currently existing field in the oringial tables that indicates which database it is in. I need to create one to be included in the view.

So could I do this?:

CREATE VIEW v_SampleView
AS
SELECT Column1, Column2, Column3, 'FromDatabase'
FROM DB1.dbo.Table1
SET FromDatabase = '_value1_'
UNION
SELECT Column1, Column2, Column3, 'FromDatabase'
FROM DB2.dbo.Table1
SET FromDatabase = '_value2_'

Would that work?

Also, these tables have like 60 columns. Is there a way to just select * from them and then add on one more field that I can define and set insetad of selecting Column1, Column2...Column60?

Thanks guys (&gals)!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-10 : 14:22:26

Your example will not work. The view that I provided (with Jeff's modification) will work for you. The column does not have to exist in the table for you to know which database it came from. In my example, 'From DB1' will act like a column even though it doesn't exist in the table. The value can be changed to anything.

Yes you can use SELECT *, but why would you want to anyway? Most of the time, you do not want to see all of the column's data so for performance reasons, you should explicitly only select which columns that you want. I seriously doubt that you need to look at ALL 60 columns.

Tara

kramerd1506
Starting Member

8 Posts

Posted - 2003-06-10 : 14:29:19

Tara, et. al.

Thanks! I got it working. This place is great. Now I just need to figure out which fields I really need to report off of and pair it down.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources