I am looking to replicate the results of a query, is that possible or do I need to rep the tables themselves?
Add a comment
|
2 Answers
Yes its definately possible. We use it all the time for replicating warehouse data to 53 different warehouses. The query you are talking about is called a replication filter.
Now listen up!!! Do not put your query in the filter. Use a view instead.
That means you can easily alter the view at a later date as filters cannot be changed. So you filter will be "select cols from vwUserFilter"
If you put the query in the filter and want to change it, you'd have to drop the publication and re-create it which is very painful.
Also see this link
answered Nov 25, 2011 at 14:36
-
3Good tip on using the view to prevent recreating the publication. Commented Nov 25, 2011 at 15:48
-
Using a view don't I have to replicate the underlying tables? That's what I learned from trying to replicate a view earlier. I noticed the filter but I didn't think I could use that as it seems to need some table to start with. Commented Nov 25, 2011 at 16:45
-
Off course you need to replicate the tables. Views are nothing but a persistent alias of a select statement (except for when they have an index, off course).– MarianCommented Nov 25, 2011 at 23:03
-
@HolidayHarry No you don't. The filtering is done at the server, not at the client. The primary database will only send down data to the client db's that pass the filters. Commented Nov 29, 2011 at 14:04
-
Has someone used a view for merge replication filtering? I cannot edit my filter with the level of control required for this. I am using SQL 2012. I can only edit after the where clause and thus cannot specify a view. Is there a blog explaining this?– peterCommented Jun 28, 2012 at 22:11
To replicate the results of a query, store the results in a table, and replicate that table. Yes the table must exist on the other servers. I suggest using snapshot replication for this type of scenario.
