dnlnln
commented
1 year ago Apologies for not responding to this earlier. There are a few different options:
- Skip change detection altogether by specifying the following parameter to sp_generate_merge:
@update_only_if_changed=0 - Change the database (or just the relevant columns) to a case-sensitive collation e.g.
SQL_Latin1_General_CP1_CS_AS - If the source and target tables are within the same SQL instance and you don't mind adding a new column to the target table, switch to hash-based change detection by specifying the
@hash_compare_column='columnName'and @include_values = 0` params
How can you update with sensitive values? For example SET IDENTITY_INSERT [dbo].[LenderLevel] ON Current value of Test ColumB is Other. It doesn't get updated with OTHER when this is run.
DECLARE @mergeOutput TABLE ( [DMLAction] VARCHAR(6) ); MERGE INTO [dbo].[Test] AS [Target] USING (VALUES (1,N'Branch',N'F',N'LENDERBR',N'BRANCH') ,(2,N'Mortgage Center',N'F',N'LENDERMC',N'MORTGAGE_CENTER') ,(3,N'Lender Head Office',N'F',N'LENDERHO',N'LENDER_HEAD_OFFICE') ,(4,N'Central Discharge Unit',N'F',NULL,N'CENTRAL_DISCHARGE_UNIT') ,(5,N'Region',N'F',NULL,N'REGION') ,(6,N'District',N'F',NULL,N'DISTRICT') ,(7,N'Other',N'F',NULL,N'OTHER') ) AS [Source] ([TestId],[Test],[IsDeleted],[ColumnA],[ColumnB]) ON ([Target].[TestId] = [Source].[TestId]) WHEN MATCHED AND ( NULLIF([Source].[Test], [Target].[Test]) IS NOT NULL OR NULLIF([Target].[Test], [Source].[Test]) IS NOT NULL OR NULLIF([Source].[IsDeleted], [Target].[IsDeleted]) IS NOT NULL OR NULLIF([Target].[IsDeleted], [Source].[IsDeleted]) IS NOT NULL OR NULLIF([Source].[ColumnA], [Target].[ColumnA]) IS NOT NULL OR NULLIF([Target].[ColumnA], [Source].[ColumnA]) IS NOT NULL OR NULLIF([Source].[ColumnB], [Target].[ColumnB]) IS NOT NULL OR NULLIF([Target].[ColumnB], [Source].[ColumnB]) IS NOT NULL) THEN UPDATE SET [Target].[Test] = [Source].[Test], [Target].[IsDeleted] = [Source].[IsDeleted], [Target].[ColumnA] = [Source].[ColumnA], [Target].[ColumnB] = [Source].[ColumnB] WHEN NOT MATCHED BY TARGET THEN INSERT([TestId],[Test],[IsDeleted],[ColumnA],[ColumnB]) VALUES([Source].[TestId],[Source].[Test],[Source].[IsDeleted],[Source].[ColumnA],[Source].[ColumnB])