Home / server / Questions / 108874
Accepted
Refracted Paladin
Asked: 2010-02-03 10:46:03 +0800 CST

Filtering Replication Sets for Top 2

  • 772

I have a Replication Set that we use in Merge Replication(SQL 2005). I am trying to figure out how to apply a Filter of Top 2 to a table.

Also, is there a way to set up a Filter so that it trickles down to sub-tables or would I need to handle that differently?

What I am looking at is a Database of Plans that I am Replicating to our users' laptops. I would like to only Replicate the last two Plans and only replicate the associated data for those two plans. Example, there is a Medication table that lists all the Medication associated to a certain Plan. I would only want to replicate the rows on the Medication table that are tied to one of the Top 2 plans.

I am not sure how I should go about this. I see the Filter option in the Publication Properties but it is by table and I don't seem to have the option of Top 2.

sql-server sql-server-2005 replication merge-replication

1 Answers

  1. Best Answer

    Filtering can only be done via the WHERE clause. What you would need to put into the filter is something like this.

    WHERE PlanId IN (SELECT TOP (2) PlanId FROM Plan ORDER BY CreateDate DESC)

    If you put that into the filter for each table it will only replicate the rows for those 2 plans.

    To use multiple columns for the filter, something like this "should" work. I haven't tested this, so it might take some tweaking to make it happen.

    WHERE PlanId IN (   
        SELECT PlanId FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY ClientId, PlanId ORDER BY CreateDate DESC) RowId,
            PlanId, ClientId
            FROM [Plan] a
            where [Plan].PlanId = a.PlanId
                and [Plan].ClientId = a.ClientID
            ) b
        WHERE b.RowId IN (1,2)
        )
    • 1