Complex comparisons/where clause question

[j.salmon]

Scenario: Suppose you have an order table (Orders) and you want to select all rows for orders in the past 90 days.

For the WhereClause on the orders table I would use something like:
OrderDate >= '2007-09-19' AND OrderDate <= '2007-12-18'

At this point, we have all records in the Orders table for the past 90 days. Now, let's assume there is a Notes table (could be any child/related table) that is tied by the order number. I only want to return records from the Server1.Notes table in which the OrderNumber exists in the Server2.Orders table.

Is this possible? Can you create a more complex join or where clause to support this kind of data copying?

[j.salmon]

I found a past post here (SQL Data Compare Past Versions forum) with the same request.

Could anyone point me in the direction of an example of how to use an indexed view within the code for a comparison operation?

Thanks!

[richardjm]

If you have linked servers you can directly reference the table on Server2 from within Server1. So your where clause would say something like...

[j.salmon]

True, but linked servers don't work when it's setup as a warm standby server (standby mode).

[richardjm]

I think whatever you're going to do is going to require a linked server in your case as there must be a way of getting the data from the remote sever into your select statement.

A nasty work-around would be to use data compare and mapping to copy the contents of the remote Orders table into a local OrdersFromServer2 or something table and then use that table in the WHERE clause of the comparison.
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd