Hi all,
We ran into an issue with history settings in combination with the DW_SourceCode. We need the DW_SourceCode in our primary key as there is overlap in the sources and this ensures uniqueness.
In the history settings we cannot choose the DW_SourceCode to be the natural key:
Without this, history does seem to work, but when we enable Create new record when a deleted record reappears in the source
We obtain the error
Error during Data Processing for Table: Maintenance.Verkoopinfo at: Maintenance
The column name 'DW_SourceCode' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.
I've looked into the stored procedure that produces this error and this snippet produces the error:
-- History: handle deleted records
INSERT INTO [DMA].[ERP_TABLE]
(
[_pk_ProduktgroepId]
, [_pk_Produktnr]
, [_pk_Produktsoort]
, [_pk_Produktsubgroep]
, [DW_SourceCode] -----------HERE-----------------------------------
, [SCD From DateTime]
, [SCD Is TombStone]
, [DW_Batch]
, [DW_SourceCode] -----------HERE---------------------
, [DW_TimeStamp]
)
SELECT
[_pk_ProduktgroepId]
, [_pk_Produktnr]
, [_pk_Produktsoort]
, [_pk_Produktsubgroep]
, [DW_SourceCode] ------------------HERE----------------------
, @LoadDate AS [SCD From DateTime]
, 1 AS [SCD Is TombStone]
, @version AS [DW_Batch]
, V.[DW_SourceCode] ------------------HERE--------------------
, @LoadDate AS [DW_TimeStamp]
FROM #HistoryMatch H
INNER JOIN [DMA].[ERP_TABLE] V ON
H.[DW_Id] = V.[DW_Id]
WHERE
H.[Delete] = 1
AND V.[SCD Is TombStone] = 0
DW_SourceCode seems to be inserted twice, which is of course not possible. However it is i.e. the same value so I believe one insert would suffice.
We cannot just clone the DW_SourceCode as then we have an issue with mismatching keys for incremental loading.
So, is it at all possible to use the DW_SourceCode in combination with History?
