I need an SQL query to return all rows of data from Table A that either do not exist in Table B or have been altered (it could be any number of fields). As you can see, I am trying to perform a type of data replication, and also (don't cringe), this query will be initially running in MS Access.
I'll illustrate a way to perform simple, one-way replication using SQL with an Employee table and a separate destination Employee table. Here are the SQL statements to create the two tables and populate the master:
create table Employees ( ID numeric primary key, FirstName varchar(30), LastName varchar(30), Title varchar(60) ); create table ReplicatedEmployees ( ID numeric primary key, FirstName varchar(30), LastName varchar(30), Title varchar(60) );In order to identify rows in the Employee table which are new since the last synchronization, we could build a view like this:
create view NewEmployees as select * from Employees where not exists ( select * from ReplicatedEmployees where ID = Employees.ID );Changed employees can be identified with a view using a MINUS keyword:
create view ChangedEmployees as select * from ( select * from Employees minus select * from ReplicatedEmployees ) chemps where exists ( select * from ReplicatedEmployees where ID = chemps.ID );If MINUS or a similar set function is not available, you can create the view by comparing each set of columns. Here's the SQL:
create view ChangedEmployees as select e.* from Employees e, ReplicatedEmployees er where e.ID = er.ID and ( e.FirstName <> er.FirstName or e.LastName <> er.LastName or e.Title <> er.Title or ( e.FirstName is null and er.FirstName is not null ) or ( e.FirstName is not null and er.FirstName is null ) or ( e.LastName is null and er.LastName is not null ) or ( e.LastName is not null and er.LastName is null ) or ( e.Title is null and er.Title is not null ) or ( e.Title is not null and er.Title is null ) );To find removed rows, a view like this will do the job:
create view RemovedEmployees as select * from ReplicatedEmployees where not exists ( select * from Employees where ID = ReplicatedEmployees.ID );To synchronize the Employee rows, the three following SQL statements should work nicely. Our previously created views make the statements much easier to read:
insert into ReplicatedEmployees select * from NewEmployees; update ReplicatedEmployees er set FirstName = ( select FirstName from Employees where ID = er.ID ), LastName = ( select LastName from Employees where ID = er.ID ), Title = ( select Title from Employees where ID = er.ID) where ID in ( select ID from ChangedEmployees ); delete from ReplicatedEmployees where ID in ( select ID from RemovedEmployees );I should note that database management systems can vary greatly in what they allow when updating one table using values from another. If the method shown here does not work, reference the documentation for your particular SQL implementation.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.