I’m trying to determine a good way to handle the scenario below. I have the following two database tables, along with sample data. Table1 contains distributions that are grouped per project. A project can have one or more distributions. A distribution can have one of more accounts. An account has a percentage allocated to it. The distributions can be modified by adding or removing account, as well as changing percentages.

Table2 tracks distributions, assigning a version number to each distribution. I need to be able to copy new distributions from Table1 to Table2, but only under two conditions:

1. the entire distribution does not already exist 
2. the distribution has been modified (accounts added/removed or percentages changed).

Note: When copying a distribution from Table1 to Table2 I need to compare all accounts and percentages within the distribution to determine if it already exists. When inserting the new distribution then I need to increment the VersionID (max(VersionID) + 1).

So, in the example provided the distribution (12345, 1) has been modified, adding account number 7, as well as changing percentages allocated. The entire distribution should be copied to the second table, incrementing the VersionID to 3 in the process.

The database in question is SQL Server 2005.

Table1
------
ProjectID   AccountDistributionID   AccountID   Percent
12345       1                       1           25.0
12345       1                       2           25.0
12345       1                       7           50.0
56789       2                       3           25.0
56789       2                       4           25.0
56789       2                       5           25.0
56789       2                       6           25.0

Table2
------
ID  VersionID   Project ID  AccountDistributionID   AccountID   Percent
1    1           12345       1                       1           50.0
2    1           12345       1                       2           50.0
3    2           56789       2                       3           25.0
4    2           56789       2                       4           25.0
5    2           56789       2                       5           25.0
6    2           56789       2                       6           25.0