using SDK to select sub-set of data
karen.clarke
Posts: 2
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?
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?
Comments
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/Filt ... a_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/C ... 000297.htm
Hopefully this helps!