Problem solve Get help with specific problems with your technologies, process and projects.

Simple one-way replication

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
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
select *
  from ( select * from Employees
         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
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
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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.