In recent months, we have converted to a new software application. The migration team (vendor) left the old database intact. They converted some of the old data to an "archive" database using the new-vendor's database structure. (That way the new software can be used to access the archived data--thereby eliminating the need for old-software licenses.) Since the conversion, our company has entered data into the new database.
I need to pull data from all THREE databases (on two SQLserver2005 servers). I would like to use a "union" query to pull all the data and automatically eliminate any duplicate records.
I've gone on "google quests" and done some reading. One option is linked servers. Another option I came across is OPENDATASOURCE.
My three databases are located on two SQLservers:
- "Server Box1" : houses server FAYRMS1 (SQL Server 9.0.3042),one database of old legacy data, named VSI_DATA
- "Server Box2" : houses server FAYOSSIRMS (SQL Server 9.0.1399), one database of archive data, named rmsconv AND one database of "live" data, named rms
I can "connect" to all three databases individually (Windows Authentication). When connected to each specific database, I can execute a query that returns the data I need. I have trouble when I am connected to one database and I try to pull data from another database. (I tried creating fully-qualified names in my FROM clause--I got errors. I queried sys.servers and discovered that the databases aren't linked.)
I've googled and done a lot of reading, but I haven't found an answer. What is the syntax to do a UNION query that will pull data from 3 databases?