How can I access 2 databases in single SQL statement (table names, DB names the same) OLEDB

Question

I have several versions of a database on the same computer. These have the same DB Name, Fields, and table names. I am trying to merge them as different DB's may have some differences (ie additional records, or field value changes)

I have 2 connections to 2 different databases on my PC (not using a server)

I would like to access 2 DBs to get the differences using SQL like:

(SELECT * FROM [].DB1Table  EXCEPT  SELECT * FROM [].DB2Table)
 UNION ALL 
(SELECT * FROM [].DB2Table  EXCEPT SELECT * FROM [].DB1Table)

I have seen solutions such as: (this is included to show one example of how to qualify tables to access 2 different tables.)

select t1.ID, t1.Name, t2.Name
from [DBOne].[dbo].[TableOne] as t1
inner join [DBbTwo].[dbo].[TableTwo] as t2 on t1.ID = t2.ID

I am trying to get the differences between the 2 tables [SELECT FROM DB1Table EXCEPT SELECT * FROM DB2Table) and then go through and merge the differences, but I am unsure how to do this in VB Net with my OLEDB DB Connections. (Both databases are Access Databases)

Is there a way to accomplish this?

The connection connects to one DB. There are ways to link and import tables in other DBs, but it is unclear what you are trying to do. A JOIN and a UNION are 2 different things. Also, hover over your tag choices and reconsider. Please read How to Ask and take the tour — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Oct 8, 2017 at 22:24
you can't compare tables using single query if they are located on different sql servers. — A Kunin
– A Kunin, Commented Oct 8, 2017 at 23:03
@AKunin You can with linked servers, or as shown in Parfait's answer. — Andrew Morton
– Andrew Morton, Commented Oct 9, 2017 at 15:49

Parfait · Accepted Answer · 2017-10-08 23:24:46Z

1

Consider connecting to the external database inline to the query which is an available feature in the MS Access SQL dialect. And use NOT EXISTS clause to retrieve unmatched records. Below assumes one connection to DB1 where DB2's table is referenced externally.

SELECT * FROM myTable t1
WHERE NOT EXISTS 
  (SELECT 1 FROM myTable t2 IN 'C:\Path\To\External\Database.accdb'
   WHERE t1.ID = t2.ID)

UNION

SELECT * FROM myTable t2 IN 'C:\Path\To\External\Database.accdb' 
WHERE NOT EXISTS 
  (SELECT 1 FROM myTable t1
   WHERE t2.ID = t1.ID)

edited Oct 8, 2017 at 23:24

answered Oct 8, 2017 at 23:15

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alan Over a year ago

I'm trying this out - will report back

Collectives™ on Stack Overflow

How can I access 2 databases in single SQL statement (table names, DB names the same) OLEDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related