Options
Complex comparisons/where clause question
j.salmon
Posts: 44 Bronze 2
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?
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?
Comments
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!
You can create a linked server using Enterprise manager or lookup sp_addlinkedserver in sql books online.
HTH
Project Manager
Red Gate Software Ltd
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.
Project Manager
Red Gate Software Ltd