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
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET DATEFORMAT YMD
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
-- Add row to [dbo].[vwDownloadHerd]
SET IDENTITY_INSERT [dbo].[vwDownloadHerd] ON
INSERT INTO [dbo].[vwDownloadHerd] ([intHerdID]) VALUES (22)
SET IDENTITY_INSERT [dbo].[vwDownloadHerd] OFF
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?