Red Gate forums :: View topic - using SDK to select sub-set of data
Return to www.red-gate.com RSS Feed Available

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

using SDK to select sub-set of data

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
karen.clarke



Joined: 16 Nov 2009
Posts: 1

PostPosted: Mon Nov 16, 2009 5:22 pm    Post subject: using SDK to select sub-set of data Reply with quote

I need to download only a subset of the data in my database. I have been using the delegate syntax to compare individual rows to determine wether to include and up to now this has worked well. However the size of the database has grown substantially and this is now becoming unworkable and very slow.

I have tried using the 'WHERE' clause on the mapping to restrict the data and this works very well. However since i have a complex relational database with many association tables it is difficult to add a where clause on many of the tables directly.

I would like to be create mappings using views where i have more control over the data included. However when I create the ExecutionBlock the script tries to turn the identity field on in the view rather than the base table (tblHerd)....as one might expect!

SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

-- Add row to [dbo].[vwDownloadHerd]
SET IDENTITY_INSERT [dbo].[vwDownloadHerd] ON
INSERT INTO [dbo].[vwDownloadHerd] ([intHerdID]) VALUES (22)
SET IDENTITY_INSERT [dbo].[vwDownloadHerd] OFF

COMMIT TRANSACTION
GO

If I amend identity_inserts manually to tblHerd (the base table)....then this script works fine when executed manually.

Is it possible to change the ExecutionBlock through the SDK?
Is is possible to use views in the data comparison to restrict the data set being compared prior to inserting data?
Any other suggestions for restricting data to be compared with a complex database structure?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Tue Nov 17, 2009 6:43 pm    Post subject: Reply with quote

Hi Karen,

This is a tricky question, but because of complexities like the one you're running into , filtering using views may not be practical for you. Realistically, the only filtering options that are going to reduce the data being compared are the where clause and the mappings. There are a number of ways to post-filter compared data before synchronization, which I discuss on our wiki here: http://labs.red-gate.com/index.php/Filtering_data_overview

The ExecutionBlock object was not designed to be edited. A possible workaround could be to implement your own code to execute the batches in the block one by one and replace or omit any transactions you don't want or add transactions that aren't included in the block:

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Comparison%20SDK&c=knowledgebase%5cSQL_Comparison_SDK%5cKB200809000297.htm

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