Complex comparisons/where clause question

Forum for users of SQL Toolkit 3,4,and 5

Complex comparisons/where clause question

Postby j.salmon » Tue Dec 18, 2007 9:24 pm

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
 
Posts: 34
Joined: Wed Aug 08, 2007 1:53 pm

Postby j.salmon » Tue Dec 18, 2007 10:10 pm

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!
j.salmon
 
Posts: 34
Joined: Wed Aug 08, 2007 1:53 pm

Postby richardjm » Wed Dec 19, 2007 10:12 am

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

Code: Select all
OrderNumber in (select OrderNumber from [Server2]DB.dbo.Orders where OrderDate between 'blah' and 'blah')


You can create a linked server using Enterprise manager or lookup sp_addlinkedserver in sql books online.

HTH
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby j.salmon » Wed Dec 19, 2007 2:08 pm

True, but linked servers don't work when it's setup as a warm standby server (standby mode).
j.salmon
 
Posts: 34
Joined: Wed Aug 08, 2007 1:53 pm

Postby richardjm » Wed Dec 19, 2007 2:20 pm

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
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests