1

I'm writing a Visual Studio SQL database project for an existing database.

I'm generating simple merge scripts per table for standing data, existing data so on and so forth. It's been relatively straight forward until I come to one particular table which is giving me the error in the title.

Now, if this was a complex query I'd understand, but it's not, it has one key field with a clustered index and no complex operations. I have tried removing all of the ISNULL checks from the MATCHED check after the first AND and it still gives me the same error. I don't understand!

The key is [my_table_code], the first column. Here is the bulk of the query with only the first row for clarity.

MERGE INTO [my_table] AS Target
USING (VALUES
  (N'00001L',N'20160425',N'20160425',N'132011',N'DMTEAM',N'C',N'Name',N'F',365,0,N'19790702',N'20130418',N'20130418',N'20130418',N'20130418',N'',0.0,0.0,0,N'GBP',1.0,N'19790702',N'N',N'N',0,0,0,0,N'Y',N'SLAC Life Series 1',N'19790703',365,N'UK',-1,N'N',N'H',N'N',0,N'',N'ALL',N'10000101',N'',0,0,N'0',N'N',N'N',0.000000000000000e+000,N'',N'E',365,0,0,N'N',365,0,0,N'N',0,0,N'',N'',0,N'',N'DEF',N'',N'',0,0,N'',N'',-1,N'   ',N'      ',N'   ',N' ',N'          ',N'E',N'       ',N'',N'          ',N'       ',N' ',0,1.5,4.5,7.5,0.0,0,N'0',N'0')
 -- 2600 rows more
) AS Source ([my_table_code],[my_table_create_date],[my_table_last_updt_date],[my_table_last_updt_time],[my_table_last_updt_userid],[my_table_record_status],[my_table_name],[my_table_next_or_last_price],[my_table_pricing_frequency],[my_table_sell_pricing_frequency],[my_table_commenced_date],[my_table_closed_date],[my_table_closed_for_newbus_date],[my_table_closed_for_spi_date],[my_table_closed_for_money_date],[my_table_category],[my_table_spread_relationship],[my_table_management_charge],[my_table_sellout_period_mths],[my_table_currency],[my_table_price_tolerance],[my_table_initial_price_date],[my_table_last_date_in_period],[my_table_sell_last_date_in_period],[my_table_last_day_in_period],[my_table_sell_last_day_in_period],[my_table_same_day_in_period],[my_table_sell_same_day_in_period],[my_table_use_working_days],[my_table_external_id],[my_table_post_initial_price_date],[my_table_price_applies_frequency],[my_table_price_country_code],[my_table_non_working_price_days_adj],[my_table_pricing_calendar],[my_table_risk_profile],[my_table_pay_dividend],[my_table_dividend_frequency],[my_table_sedol],[my_table_type],[my_table_strike_date],[my_table_maturity_date],[my_table_amc_basis_no],[my_table_coupon_select_id],[my_table_coupon_defaults],[my_table_whole_units],[my_table_clone_by_policy],[my_table_initial_clone_price],[my_table_price_base_calc_date],[my_table_group],[my_table_disinv_notice_freq],[my_table_disinv_notice_value],[my_table_disinv_same_day_in_period],[my_table_disinv_notice_last_date],[my_table_trade_cutoff_freq],[my_table_trade_cutoff_value],[my_table_trade_cutoff_same_day_in_period],[my_table_trade_cutoff_last_date],[my_table_expected_delay_rules],[my_table_settlement_days],[my_table_bid_offer_exception_rules],[my_table_holding_fund_id],[my_table_timeout_days],[my_table_price_valuation_basis],[my_table_classification],[my_table_target_days],[my_table_sell_target_days],[my_table_disinv_same_day_period_skip],[my_table_trade_cutoff_same_day_period_skip],[my_table_equity_sector],[my_table_client_classification],[my_table_max_decimal_places],[my_table_rounding_method],[my_table_closing_time],[my_table_rounding_direction],[my_table_use_external_WS],[my_table_gl_code],[my_table_asset_class],[my_table_grouping],[my_table_factsheet_url],[my_table_aladdin_ticker],[my_table_manager],[my_table_use_notice_for_backdates],[my_table_business_date_delay],[my_table_growth_low],[my_table_growth_med],[my_table_growth_high],[my_table_growth_SMPI],[my_table_instruction_file_decimal_places],[my_table_suspend_start_date],[my_table_suspend_end_date])
ON (Target.[my_table_code] = Source.[my_table_code])
WHEN MATCHED AND (
    NULLIF(Source.[my_table_create_date], Target.[my_table_create_date]) IS NOT NULL OR NULLIF(Target.[my_table_create_date], Source.[my_table_create_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_last_updt_date], Target.[my_table_last_updt_date]) IS NOT NULL OR NULLIF(Target.[my_table_last_updt_date], Source.[my_table_last_updt_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_last_updt_time], Target.[my_table_last_updt_time]) IS NOT NULL OR NULLIF(Target.[my_table_last_updt_time], Source.[my_table_last_updt_time]) IS NOT NULL OR 
    NULLIF(Source.[my_table_last_updt_userid], Target.[my_table_last_updt_userid]) IS NOT NULL OR NULLIF(Target.[my_table_last_updt_userid], Source.[my_table_last_updt_userid]) IS NOT NULL OR 
    NULLIF(Source.[my_table_record_status], Target.[my_table_record_status]) IS NOT NULL OR NULLIF(Target.[my_table_record_status], Source.[my_table_record_status]) IS NOT NULL OR 
    NULLIF(Source.[my_table_name], Target.[my_table_name]) IS NOT NULL OR NULLIF(Target.[my_table_name], Source.[my_table_name]) IS NOT NULL OR 
    NULLIF(Source.[my_table_next_or_last_price], Target.[my_table_next_or_last_price]) IS NOT NULL OR NULLIF(Target.[my_table_next_or_last_price], Source.[my_table_next_or_last_price]) IS NOT NULL OR 
    NULLIF(Source.[my_table_pricing_frequency], Target.[my_table_pricing_frequency]) IS NOT NULL OR NULLIF(Target.[my_table_pricing_frequency], Source.[my_table_pricing_frequency]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sell_pricing_frequency], Target.[my_table_sell_pricing_frequency]) IS NOT NULL OR NULLIF(Target.[my_table_sell_pricing_frequency], Source.[my_table_sell_pricing_frequency]) IS NOT NULL OR 
    NULLIF(Source.[my_table_commenced_date], Target.[my_table_commenced_date]) IS NOT NULL OR NULLIF(Target.[my_table_commenced_date], Source.[my_table_commenced_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_closed_date], Target.[my_table_closed_date]) IS NOT NULL OR NULLIF(Target.[my_table_closed_date], Source.[my_table_closed_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_closed_for_newbus_date], Target.[my_table_closed_for_newbus_date]) IS NOT NULL OR NULLIF(Target.[my_table_closed_for_newbus_date], Source.[my_table_closed_for_newbus_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_closed_for_spi_date], Target.[my_table_closed_for_spi_date]) IS NOT NULL OR NULLIF(Target.[my_table_closed_for_spi_date], Source.[my_table_closed_for_spi_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_closed_for_money_date], Target.[my_table_closed_for_money_date]) IS NOT NULL OR NULLIF(Target.[my_table_closed_for_money_date], Source.[my_table_closed_for_money_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_category], Target.[my_table_category]) IS NOT NULL OR NULLIF(Target.[my_table_category], Source.[my_table_category]) IS NOT NULL OR 
    NULLIF(Source.[my_table_spread_relationship], Target.[my_table_spread_relationship]) IS NOT NULL OR NULLIF(Target.[my_table_spread_relationship], Source.[my_table_spread_relationship]) IS NOT NULL OR 
    NULLIF(Source.[my_table_management_charge], Target.[my_table_management_charge]) IS NOT NULL OR NULLIF(Target.[my_table_management_charge], Source.[my_table_management_charge]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sellout_period_mths], Target.[my_table_sellout_period_mths]) IS NOT NULL OR NULLIF(Target.[my_table_sellout_period_mths], Source.[my_table_sellout_period_mths]) IS NOT NULL OR 
    NULLIF(Source.[my_table_currency], Target.[my_table_currency]) IS NOT NULL OR NULLIF(Target.[my_table_currency], Source.[my_table_currency]) IS NOT NULL OR 
    NULLIF(Source.[my_table_price_tolerance], Target.[my_table_price_tolerance]) IS NOT NULL OR NULLIF(Target.[my_table_price_tolerance], Source.[my_table_price_tolerance]) IS NOT NULL OR 
    NULLIF(Source.[my_table_initial_price_date], Target.[my_table_initial_price_date]) IS NOT NULL OR NULLIF(Target.[my_table_initial_price_date], Source.[my_table_initial_price_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_last_date_in_period], Target.[my_table_last_date_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_last_date_in_period], Source.[my_table_last_date_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sell_last_date_in_period], Target.[my_table_sell_last_date_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_sell_last_date_in_period], Source.[my_table_sell_last_date_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_last_day_in_period], Target.[my_table_last_day_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_last_day_in_period], Source.[my_table_last_day_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sell_last_day_in_period], Target.[my_table_sell_last_day_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_sell_last_day_in_period], Source.[my_table_sell_last_day_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_same_day_in_period], Target.[my_table_same_day_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_same_day_in_period], Source.[my_table_same_day_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sell_same_day_in_period], Target.[my_table_sell_same_day_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_sell_same_day_in_period], Source.[my_table_sell_same_day_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_use_working_days], Target.[my_table_use_working_days]) IS NOT NULL OR NULLIF(Target.[my_table_use_working_days], Source.[my_table_use_working_days]) IS NOT NULL OR 
    NULLIF(Source.[my_table_external_id], Target.[my_table_external_id]) IS NOT NULL OR NULLIF(Target.[my_table_external_id], Source.[my_table_external_id]) IS NOT NULL OR 
    NULLIF(Source.[my_table_post_initial_price_date], Target.[my_table_post_initial_price_date]) IS NOT NULL OR NULLIF(Target.[my_table_post_initial_price_date], Source.[my_table_post_initial_price_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_price_applies_frequency], Target.[my_table_price_applies_frequency]) IS NOT NULL OR NULLIF(Target.[my_table_price_applies_frequency], Source.[my_table_price_applies_frequency]) IS NOT NULL OR 
    NULLIF(Source.[my_table_price_country_code], Target.[my_table_price_country_code]) IS NOT NULL OR NULLIF(Target.[my_table_price_country_code], Source.[my_table_price_country_code]) IS NOT NULL OR 
    NULLIF(Source.[my_table_non_working_price_days_adj], Target.[my_table_non_working_price_days_adj]) IS NOT NULL OR NULLIF(Target.[my_table_non_working_price_days_adj], Source.[my_table_non_working_price_days_adj]) IS NOT NULL OR 
    NULLIF(Source.[my_table_pricing_calendar], Target.[my_table_pricing_calendar]) IS NOT NULL OR NULLIF(Target.[my_table_pricing_calendar], Source.[my_table_pricing_calendar]) IS NOT NULL OR 
    NULLIF(Source.[my_table_risk_profile], Target.[my_table_risk_profile]) IS NOT NULL OR NULLIF(Target.[my_table_risk_profile], Source.[my_table_risk_profile]) IS NOT NULL OR 
    NULLIF(Source.[my_table_pay_dividend], Target.[my_table_pay_dividend]) IS NOT NULL OR NULLIF(Target.[my_table_pay_dividend], Source.[my_table_pay_dividend]) IS NOT NULL OR 
    NULLIF(Source.[my_table_dividend_frequency], Target.[my_table_dividend_frequency]) IS NOT NULL OR NULLIF(Target.[my_table_dividend_frequency], Source.[my_table_dividend_frequency]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sedol], Target.[my_table_sedol]) IS NOT NULL OR NULLIF(Target.[my_table_sedol], Source.[my_table_sedol]) IS NOT NULL OR 
    NULLIF(Source.[my_table_type], Target.[my_table_type]) IS NOT NULL OR NULLIF(Target.[my_table_type], Source.[my_table_type]) IS NOT NULL OR 
    NULLIF(Source.[my_table_strike_date], Target.[my_table_strike_date]) IS NOT NULL OR NULLIF(Target.[my_table_strike_date], Source.[my_table_strike_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_maturity_date], Target.[my_table_maturity_date]) IS NOT NULL OR NULLIF(Target.[my_table_maturity_date], Source.[my_table_maturity_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_amc_basis_no], Target.[my_table_amc_basis_no]) IS NOT NULL OR NULLIF(Target.[my_table_amc_basis_no], Source.[my_table_amc_basis_no]) IS NOT NULL OR 
    NULLIF(Source.[my_table_coupon_select_id], Target.[my_table_coupon_select_id]) IS NOT NULL OR NULLIF(Target.[my_table_coupon_select_id], Source.[my_table_coupon_select_id]) IS NOT NULL OR 
    NULLIF(Source.[my_table_coupon_defaults], Target.[my_table_coupon_defaults]) IS NOT NULL OR NULLIF(Target.[my_table_coupon_defaults], Source.[my_table_coupon_defaults]) IS NOT NULL OR 
    NULLIF(Source.[my_table_whole_units], Target.[my_table_whole_units]) IS NOT NULL OR NULLIF(Target.[my_table_whole_units], Source.[my_table_whole_units]) IS NOT NULL OR 
    NULLIF(Source.[my_table_clone_by_policy], Target.[my_table_clone_by_policy]) IS NOT NULL OR NULLIF(Target.[my_table_clone_by_policy], Source.[my_table_clone_by_policy]) IS NOT NULL OR 
    NULLIF(Source.[my_table_initial_clone_price], Target.[my_table_initial_clone_price]) IS NOT NULL OR NULLIF(Target.[my_table_initial_clone_price], Source.[my_table_initial_clone_price]) IS NOT NULL OR 
    NULLIF(Source.[my_table_price_base_calc_date], Target.[my_table_price_base_calc_date]) IS NOT NULL OR NULLIF(Target.[my_table_price_base_calc_date], Source.[my_table_price_base_calc_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_group], Target.[my_table_group]) IS NOT NULL OR NULLIF(Target.[my_table_group], Source.[my_table_group]) IS NOT NULL OR 
    NULLIF(Source.[my_table_disinv_notice_freq], Target.[my_table_disinv_notice_freq]) IS NOT NULL OR NULLIF(Target.[my_table_disinv_notice_freq], Source.[my_table_disinv_notice_freq]) IS NOT NULL OR 
    NULLIF(Source.[my_table_disinv_notice_value], Target.[my_table_disinv_notice_value]) IS NOT NULL OR NULLIF(Target.[my_table_disinv_notice_value], Source.[my_table_disinv_notice_value]) IS NOT NULL OR 
    NULLIF(Source.[my_table_disinv_same_day_in_period], Target.[my_table_disinv_same_day_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_disinv_same_day_in_period], Source.[my_table_disinv_same_day_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_disinv_notice_last_date], Target.[my_table_disinv_notice_last_date]) IS NOT NULL OR NULLIF(Target.[my_table_disinv_notice_last_date], Source.[my_table_disinv_notice_last_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_trade_cutoff_freq], Target.[my_table_trade_cutoff_freq]) IS NOT NULL OR NULLIF(Target.[my_table_trade_cutoff_freq], Source.[my_table_trade_cutoff_freq]) IS NOT NULL OR 
    NULLIF(Source.[my_table_trade_cutoff_value], Target.[my_table_trade_cutoff_value]) IS NOT NULL OR NULLIF(Target.[my_table_trade_cutoff_value], Source.[my_table_trade_cutoff_value]) IS NOT NULL OR 
    NULLIF(Source.[my_table_trade_cutoff_same_day_in_period], Target.[my_table_trade_cutoff_same_day_in_period]) IS NOT NULL OR NULLIF(Target.[my_table_trade_cutoff_same_day_in_period], Source.[my_table_trade_cutoff_same_day_in_period]) IS NOT NULL OR 
    NULLIF(Source.[my_table_trade_cutoff_last_date], Target.[my_table_trade_cutoff_last_date]) IS NOT NULL OR NULLIF(Target.[my_table_trade_cutoff_last_date], Source.[my_table_trade_cutoff_last_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_expected_delay_rules], Target.[my_table_expected_delay_rules]) IS NOT NULL OR NULLIF(Target.[my_table_expected_delay_rules], Source.[my_table_expected_delay_rules]) IS NOT NULL OR 
    NULLIF(Source.[my_table_settlement_days], Target.[my_table_settlement_days]) IS NOT NULL OR NULLIF(Target.[my_table_settlement_days], Source.[my_table_settlement_days]) IS NOT NULL OR 
    NULLIF(Source.[my_table_bid_offer_exception_rules], Target.[my_table_bid_offer_exception_rules]) IS NOT NULL OR NULLIF(Target.[my_table_bid_offer_exception_rules], Source.[my_table_bid_offer_exception_rules]) IS NOT NULL OR 
    NULLIF(Source.[my_table_holding_fund_id], Target.[my_table_holding_fund_id]) IS NOT NULL OR NULLIF(Target.[my_table_holding_fund_id], Source.[my_table_holding_fund_id]) IS NOT NULL OR 
    NULLIF(Source.[my_table_timeout_days], Target.[my_table_timeout_days]) IS NOT NULL OR NULLIF(Target.[my_table_timeout_days], Source.[my_table_timeout_days]) IS NOT NULL OR 
    NULLIF(Source.[my_table_price_valuation_basis], Target.[my_table_price_valuation_basis]) IS NOT NULL OR NULLIF(Target.[my_table_price_valuation_basis], Source.[my_table_price_valuation_basis]) IS NOT NULL OR 
    NULLIF(Source.[my_table_classification], Target.[my_table_classification]) IS NOT NULL OR NULLIF(Target.[my_table_classification], Source.[my_table_classification]) IS NOT NULL OR 
    NULLIF(Source.[my_table_target_days], Target.[my_table_target_days]) IS NOT NULL OR NULLIF(Target.[my_table_target_days], Source.[my_table_target_days]) IS NOT NULL OR 
    NULLIF(Source.[my_table_sell_target_days], Target.[my_table_sell_target_days]) IS NOT NULL OR NULLIF(Target.[my_table_sell_target_days], Source.[my_table_sell_target_days]) IS NOT NULL OR 
    NULLIF(Source.[my_table_disinv_same_day_period_skip], Target.[my_table_disinv_same_day_period_skip]) IS NOT NULL OR NULLIF(Target.[my_table_disinv_same_day_period_skip], Source.[my_table_disinv_same_day_period_skip]) IS NOT NULL OR 
    NULLIF(Source.[my_table_trade_cutoff_same_day_period_skip], Target.[my_table_trade_cutoff_same_day_period_skip]) IS NOT NULL OR NULLIF(Target.[my_table_trade_cutoff_same_day_period_skip], Source.[my_table_trade_cutoff_same_day_period_skip]) IS NOT NULL OR 
    NULLIF(Source.[my_table_equity_sector], Target.[my_table_equity_sector]) IS NOT NULL OR NULLIF(Target.[my_table_equity_sector], Source.[my_table_equity_sector]) IS NOT NULL OR 
    NULLIF(Source.[my_table_client_classification], Target.[my_table_client_classification]) IS NOT NULL OR NULLIF(Target.[my_table_client_classification], Source.[my_table_client_classification]) IS NOT NULL OR 
    NULLIF(Source.[my_table_max_decimal_places], Target.[my_table_max_decimal_places]) IS NOT NULL OR NULLIF(Target.[my_table_max_decimal_places], Source.[my_table_max_decimal_places]) IS NOT NULL OR 
    NULLIF(Source.[my_table_rounding_method], Target.[my_table_rounding_method]) IS NOT NULL OR NULLIF(Target.[my_table_rounding_method], Source.[my_table_rounding_method]) IS NOT NULL OR 
    NULLIF(Source.[my_table_closing_time], Target.[my_table_closing_time]) IS NOT NULL OR NULLIF(Target.[my_table_closing_time], Source.[my_table_closing_time]) IS NOT NULL OR 
    NULLIF(Source.[my_table_rounding_direction], Target.[my_table_rounding_direction]) IS NOT NULL OR NULLIF(Target.[my_table_rounding_direction], Source.[my_table_rounding_direction]) IS NOT NULL OR 
    NULLIF(Source.[my_table_use_external_WS], Target.[my_table_use_external_WS]) IS NOT NULL OR NULLIF(Target.[my_table_use_external_WS], Source.[my_table_use_external_WS]) IS NOT NULL OR 
    NULLIF(Source.[my_table_gl_code], Target.[my_table_gl_code]) IS NOT NULL OR NULLIF(Target.[my_table_gl_code], Source.[my_table_gl_code]) IS NOT NULL OR 
    NULLIF(Source.[my_table_asset_class], Target.[my_table_asset_class]) IS NOT NULL OR NULLIF(Target.[my_table_asset_class], Source.[my_table_asset_class]) IS NOT NULL OR 
    NULLIF(Source.[my_table_grouping], Target.[my_table_grouping]) IS NOT NULL OR NULLIF(Target.[my_table_grouping], Source.[my_table_grouping]) IS NOT NULL OR 
    NULLIF(Source.[my_table_factsheet_url], Target.[my_table_factsheet_url]) IS NOT NULL OR NULLIF(Target.[my_table_factsheet_url], Source.[my_table_factsheet_url]) IS NOT NULL OR 
    NULLIF(Source.[my_table_aladdin_ticker], Target.[my_table_aladdin_ticker]) IS NOT NULL OR NULLIF(Target.[my_table_aladdin_ticker], Source.[my_table_aladdin_ticker]) IS NOT NULL OR 
    NULLIF(Source.[my_table_manager], Target.[my_table_manager]) IS NOT NULL OR NULLIF(Target.[my_table_manager], Source.[my_table_manager]) IS NOT NULL OR 
    NULLIF(Source.[my_table_use_notice_for_backdates], Target.[my_table_use_notice_for_backdates]) IS NOT NULL OR NULLIF(Target.[my_table_use_notice_for_backdates], Source.[my_table_use_notice_for_backdates]) IS NOT NULL OR 
    NULLIF(Source.[my_table_business_date_delay], Target.[my_table_business_date_delay]) IS NOT NULL OR NULLIF(Target.[my_table_business_date_delay], Source.[my_table_business_date_delay]) IS NOT NULL OR 
    NULLIF(Source.[my_table_growth_low], Target.[my_table_growth_low]) IS NOT NULL OR NULLIF(Target.[my_table_growth_low], Source.[my_table_growth_low]) IS NOT NULL OR 
    NULLIF(Source.[my_table_growth_med], Target.[my_table_growth_med]) IS NOT NULL OR NULLIF(Target.[my_table_growth_med], Source.[my_table_growth_med]) IS NOT NULL OR 
    NULLIF(Source.[my_table_growth_high], Target.[my_table_growth_high]) IS NOT NULL OR NULLIF(Target.[my_table_growth_high], Source.[my_table_growth_high]) IS NOT NULL OR 
    NULLIF(Source.[my_table_growth_SMPI], Target.[my_table_growth_SMPI]) IS NOT NULL OR NULLIF(Target.[my_table_growth_SMPI], Source.[my_table_growth_SMPI]) IS NOT NULL OR 
    NULLIF(Source.[my_table_instruction_file_decimal_places], Target.[my_table_instruction_file_decimal_places]) IS NOT NULL OR NULLIF(Target.[my_table_instruction_file_decimal_places], Source.[my_table_instruction_file_decimal_places]) IS NOT NULL OR 
    NULLIF(Source.[my_table_suspend_start_date], Target.[my_table_suspend_start_date]) IS NOT NULL OR NULLIF(Target.[my_table_suspend_start_date], Source.[my_table_suspend_start_date]) IS NOT NULL OR 
    NULLIF(Source.[my_table_suspend_end_date], Target.[my_table_suspend_end_date]) IS NOT NULL OR NULLIF(Target.[my_table_suspend_end_date], Source.[my_table_suspend_end_date]) IS NOT NULL ) THEN

 UPDATE SET
  [my_table_create_date] = Source.[my_table_create_date], 
  [my_table_last_updt_date] = Source.[my_table_last_updt_date], 
  -- All fields listed in order.
  [my_table_suspend_end_date] = Source.[my_table_suspend_end_date]
WHEN NOT MATCHED BY TARGET THEN
 INSERT([my_table_code],[my_table_create_date],[my_table_last_updt_date],[my_table_last_updt_time],[my_table_last_updt_userid],[my_table_record_status],[my_table_name],[my_table_next_or_last_price],[my_table_pricing_frequency],[my_table_sell_pricing_frequency],[my_table_commenced_date],[my_table_closed_date],[my_table_closed_for_newbus_date],[my_table_closed_for_spi_date],[my_table_closed_for_money_date],[my_table_category],[my_table_spread_relationship],[my_table_management_charge],[my_table_sellout_period_mths],[my_table_currency],[my_table_price_tolerance],[my_table_initial_price_date],[my_table_last_date_in_period],[my_table_sell_last_date_in_period],[my_table_last_day_in_period],[my_table_sell_last_day_in_period],[my_table_same_day_in_period],[my_table_sell_same_day_in_period],[my_table_use_working_days],[my_table_external_id],[my_table_post_initial_price_date],[my_table_price_applies_frequency],[my_table_price_country_code],[my_table_non_working_price_days_adj],[my_table_pricing_calendar],[my_table_risk_profile],[my_table_pay_dividend],[my_table_dividend_frequency],[my_table_sedol],[my_table_type],[my_table_strike_date],[my_table_maturity_date],[my_table_amc_basis_no],[my_table_coupon_select_id],[my_table_coupon_defaults],[my_table_whole_units],[my_table_clone_by_policy],[my_table_initial_clone_price],[my_table_price_base_calc_date],[my_table_group],[my_table_disinv_notice_freq],[my_table_disinv_notice_value],[my_table_disinv_same_day_in_period],[my_table_disinv_notice_last_date],[my_table_trade_cutoff_freq],[my_table_trade_cutoff_value],[my_table_trade_cutoff_same_day_in_period],[my_table_trade_cutoff_last_date],[my_table_expected_delay_rules],[my_table_settlement_days],[my_table_bid_offer_exception_rules],[my_table_holding_fund_id],[my_table_timeout_days],[my_table_price_valuation_basis],[my_table_classification],[my_table_target_days],[my_table_sell_target_days],[my_table_disinv_same_day_period_skip],[my_table_trade_cutoff_same_day_period_skip],[my_table_equity_sector],[my_table_client_classification],[my_table_max_decimal_places],[my_table_rounding_method],[my_table_closing_time],[my_table_rounding_direction],[my_table_use_external_WS],[my_table_gl_code],[my_table_asset_class],[my_table_grouping],[my_table_factsheet_url],[my_table_aladdin_ticker],[my_table_manager],[my_table_use_notice_for_backdates],[my_table_business_date_delay],[my_table_growth_low],[my_table_growth_med],[my_table_growth_high],[my_table_growth_SMPI],[my_table_instruction_file_decimal_places],[my_table_suspend_start_date],[my_table_suspend_end_date])
 VALUES(Source.[my_table_code],Source.[my_table_create_date],Source.[my_table_last_updt_date],Source.[my_table_last_updt_time],Source.[my_table_last_updt_userid],Source.[my_table_record_status],Source.[my_table_name],Source.[my_table_next_or_last_price],Source.[my_table_pricing_frequency],Source.[my_table_sell_pricing_frequency],Source.[my_table_commenced_date],Source.[my_table_closed_date],Source.[my_table_closed_for_newbus_date],Source.[my_table_closed_for_spi_date],Source.[my_table_closed_for_money_date],Source.[my_table_category],Source.[my_table_spread_relationship],Source.[my_table_management_charge],Source.[my_table_sellout_period_mths],Source.[my_table_currency],Source.[my_table_price_tolerance],Source.[my_table_initial_price_date],Source.[my_table_last_date_in_period],Source.[my_table_sell_last_date_in_period],Source.[my_table_last_day_in_period],Source.[my_table_sell_last_day_in_period],Source.[my_table_same_day_in_period],Source.[my_table_sell_same_day_in_period],Source.[my_table_use_working_days],Source.[my_table_external_id],Source.[my_table_post_initial_price_date],Source.[my_table_price_applies_frequency],Source.[my_table_price_country_code],Source.[my_table_non_working_price_days_adj],Source.[my_table_pricing_calendar],Source.[my_table_risk_profile],Source.[my_table_pay_dividend],Source.[my_table_dividend_frequency],Source.[my_table_sedol],Source.[my_table_type],Source.[my_table_strike_date],Source.[my_table_maturity_date],Source.[my_table_amc_basis_no],Source.[my_table_coupon_select_id],Source.[my_table_coupon_defaults],Source.[my_table_whole_units],Source.[my_table_clone_by_policy],Source.[my_table_initial_clone_price],Source.[my_table_price_base_calc_date],Source.[my_table_group],Source.[my_table_disinv_notice_freq],Source.[my_table_disinv_notice_value],Source.[my_table_disinv_same_day_in_period],Source.[my_table_disinv_notice_last_date],Source.[my_table_trade_cutoff_freq],Source.[my_table_trade_cutoff_value],Source.[my_table_trade_cutoff_same_day_in_period],Source.[my_table_trade_cutoff_last_date],Source.[my_table_expected_delay_rules],Source.[my_table_settlement_days],Source.[my_table_bid_offer_exception_rules],Source.[my_table_holding_fund_id],Source.[my_table_timeout_days],Source.[my_table_price_valuation_basis],Source.[my_table_classification],Source.[my_table_target_days],Source.[my_table_sell_target_days],Source.[my_table_disinv_same_day_period_skip],Source.[my_table_trade_cutoff_same_day_period_skip],Source.[my_table_equity_sector],Source.[my_table_client_classification],Source.[my_table_max_decimal_places],Source.[my_table_rounding_method],Source.[my_table_closing_time],Source.[my_table_rounding_direction],Source.[my_table_use_external_WS],Source.[my_table_gl_code],Source.[my_table_asset_class],Source.[my_table_grouping],Source.[my_table_factsheet_url],Source.[my_table_aladdin_ticker],Source.[my_table_manager],Source.[my_table_use_notice_for_backdates],Source.[my_table_business_date_delay],Source.[my_table_growth_low],Source.[my_table_growth_med],Source.[my_table_growth_high],Source.[my_table_growth_SMPI],Source.[my_table_instruction_file_decimal_places],Source.[my_table_suspend_start_date],Source.[my_table_suspend_end_date])
WHEN NOT MATCHED BY SOURCE THEN 
 DELETE
;
GO
DECLARE @mergeError int
 , @mergeCount int
SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
IF @mergeError != 0
 BEGIN
 PRINT 'ERROR OCCURRED IN MERGE FOR [my_table]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
 END
ELSE
 BEGIN
 PRINT '[my_table] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
 END
GO    

2 Answers 2

3

That long list of conditions is pretty horrible.

You can get the same result with

WHEN MATCHED AND EXISTS
(
SELECT Source.*
EXCEPT
SELECT Target.*
)   

If both tables have the same columns and column order and you are trying to match them all.

Or alternatively

WHEN MATCHED AND EXISTS
(
SELECT Source.[my_table_create_date],
       Source.[my_table_last_updt_date],
       Source.[my_table_last_updt_time],
       Source.[my_table_last_updt_userid],
       Source.[my_table_record_status],
       Source.[my_table_name]
       /*TODO: ADD REMAINING COLUMNS - (DON'T ADD ANY FROM!)*/
EXCEPT
SELECT Target.[my_table_create_date],
       Target.[my_table_last_updt_date],
       Target.[my_table_last_updt_time],
       Target.[my_table_last_updt_userid],
       Target.[my_table_record_status],
       Target.[my_table_name]
        /*TODO: ADD REMAINING COLUMNS - (DON'T ADD ANY FROM!)*/
)   

Which I imagine will resolve your issue (if it doesn't try inserting the values into a temp table first and referencing that in the merge)

More details

1
  • 1
    Thanks for the info! I had just fixed it by using a temporary table when I saw your reply, but I had already tried removing all of the IFNULL and it didn't fix it. By switching to your first suggestion it seems to run much quicker AND it's a hell of a lot easier on the eyes. Also, about the complexity, when I said it wasn't complex I meant the out of resources error is usually thrown for JOINS exceeding 10,000 clauses in the ON clause which I saw as MUCH more complex than this could be! Dec 13, 2017 at 9:33
1

"Query processor ran out of internal resources and could not produce query plan", probably means you ran out of memory during the compilation stage, which is likely down to your query.

You say it's not complex, and writing isn't, but under the hood there is a lot more going on. MERGE also has some bugs in it:

https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

The article is quite old, but note the cases marked as "Won't fix". So long as you are aware of these, carry on using MERGE... alternatively use INSERT, UPDATE, DELETE?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.