homepage Welcome to WebmasterWorld Guest from 54.211.230.186
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Make a Recordset from 2 Different Databases
TravelSite

10+ Year Member



 
Msg#: 4006657 posted 12:41 am on Oct 14, 2009 (gmt 0)

I have Database A and Database B. Within Database A I have View C, and within Database B I have View D.

The databases and views are identical in terms of fields, tables etc - only the data changes.

Can anyone advise on how to create a large recordset containing all the records in View C + all the records in View D.

I'm not sure how to do the sql that links two seperate MSSQL databases (on same server), and don't know how to append all records together (as they aren't really being "joined" to each other on a key as such as far as I can tell?).

Thanks

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4006657 posted 3:28 pm on Oct 14, 2009 (gmt 0)

Something like the following should work with MS SQL:
SELECT *
FROM DatabaseA.[dbo].ViewC
UNION
SELECT *
FROM DatabaseB.[dbo].ViewD

The naming convention is DatabaseName.[owner].TableName (and TableName can also be ViewName).

Similarly, if you have setup a linked server, the naming convention would be:
[LinkedServerName].DatabaseName.[owner].TableName

TravelSite

10+ Year Member



 
Msg#: 4006657 posted 12:08 pm on Oct 23, 2009 (gmt 0)

That's great thanks - it solved the problem :)

Is there a way to identify which database (e.g. A or B) a given record came from (I can't add a new field to the databases as they are overwritten daily)?

mark_roach

10+ Year Member



 
Msg#: 4006657 posted 1:06 pm on Oct 23, 2009 (gmt 0)

Is there a way to identify which database (e.g. A or B) a given record came from (I can't add a new field to the databases as they are overwritten daily)?

Just add a different literal to each select.

eg
select *, 'A'
from table A
union
select *, 'B'
from table B

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved