Welcome to WebmasterWorld Guest from 54.158.227.99

Forum Moderators: open

Message Too Old, No Replies

Make a Recordset from 2 Different Databases

     
12:41 am on Oct 14, 2009 (gmt 0)

Preferred Member

10+ Year Member

joined:June 11, 2003
posts: 417
votes: 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

3:28 pm on Oct 14, 2009 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5620
votes: 44


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

12:08 pm on Oct 23, 2009 (gmt 0)

Preferred Member

10+ Year Member

joined:June 11, 2003
posts: 417
votes: 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)?

1:06 pm on Oct 23, 2009 (gmt 0)

Preferred Member

10+ Year Member

joined:July 28, 2000
posts: 580
votes: 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