Red Gate forums :: View topic - Complex comparisons/where clause question
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Complex comparisons/where clause question

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
j.salmon



Joined: 08 Aug 2007
Posts: 34

PostPosted: Tue Dec 18, 2007 9:24 pm    Post subject: Complex comparisons/where clause question Reply with quote

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?
Back to top
View user's profile Send private message
j.salmon



Joined: 08 Aug 2007
Posts: 34

PostPosted: Tue Dec 18, 2007 10:10 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 389
Location: Red Gate Software Ltd

PostPosted: Wed Dec 19, 2007 10:12 am    Post subject: Reply with quote

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:

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
Back to top
View user's profile Send private message Send e-mail
j.salmon



Joined: 08 Aug 2007
Posts: 34

PostPosted: Wed Dec 19, 2007 2:08 pm    Post subject: Reply with quote

True, but linked servers don't work when it's setup as a warm standby server (standby mode).
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 389
Location: Red Gate Software Ltd

PostPosted: Wed Dec 19, 2007 2:20 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group