I have a requirement, insert/update data from staging table stgTbl to another table T2. If exists update, no matter how many duplicates are there. Similarly, if not exists insert directly T2. Very Simple. Since the staging table stgTb1 is scheduled job everyday. Sometime I would get multiple duplicate rows as well. So because of duplicates, merge statement producing an error:
"The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row".
I tried with checksum but still getting because i may done wrongly in checksum. There is no primary key in both the tables (i removed, because otherwise i am getting primary key constraint error). Based on columns (account and ref_key2) should go with insert/update. I have tried with if else with exists also but somehow this is also not working if in case there are 0 records in the target table T2. SQL masters could solve this issue. Appreciate their knowledge share.
CREATE OR ALTER Trigger [dbo].[tr_Merge_Personal_Expense] on [dbo].
[Personal_Expense_Staging]
AFTER INSERT
AS
BEGIN
DECLARE @SummaryOfChanges TABLE(Change nvarchar(20));
MERGE [dbo].[Personal_Expense] AS TARGET
USING (
SELECT *,
CHECKSUM(
ISNULL(CONVERT(nvarchar (3) ,[Client]),'0'),
ISNULL(CONVERT(nvarchar (50),[Text]),'0'),
ISNULL(CONVERT(nvarchar (10),[Cost_Center]),'0'),
ISNULL(CONVERT(nvarchar (2) ,[Posting_Period]),'0'),
ISNULL(CONVERT(nvarchar (10),[Profit]),'0') ,
ISNULL(CONVERT(nvarchar (2) ,[Document_Type]),'0'),
ISNULL(CONVERT(nvarchar (4) ,[Company_Code]),'0') ,
-- ISNULL(CONVERT(nvarchar (10),[Account]),'0'),
ISNULL(CONVERT(nvarchar (20),[Amount_In_Doc_Curr]),'0'),
ISNULL(CONVERT(nvarchar (5) ,[Document_Currency]),'0') ,
ISNULL(CONVERT(nvarchar (20),[Amount_In_local_Curr]),'0') ,
ISNULL(CONVERT(nvarchar (20),[Amount_In_Grp_Curr]),'0'),
ISNULL(CONVERT(nvarchar (25),[Group]),'0'),
[Posting_Date],
ISNULL(CONVERT(nvarchar (18), [Assignment]),'0'),
ISNULL(CONVERT(nvarchar (16), [Reference]),'0'),
ISNULL(CONVERT(nvarchar (20), [Reference_Key1]),'0') ,
--[Reference_Key2] [nvarchar](12) NOT NULL,
ISNULL(CONVERT(nvarchar (20), [Reference_Key3]),'0') ,
ISNULL(CONVERT(nvarchar (20), [Document_Number]),'0') ,
ISNULL(CONVERT(nvarchar (25), [Document_Header_Text]),'0')
) AS [HashChecksum]
FROM
[dbo].[Personal_Expense_Staging]
) AS SOURCE
ON (
TARGET.[Reference_Key2] = SOURCE.[Reference_Key2] AND
TARGET.[Account] = SOURCE.[Account]
)
----- Update
WHEN MATCHED AND ( TARGET.[HashChecksum] <> SOURCE.[HashChecksum] )
THEN
UPDATE SET
TARGET.[client] = SOURCE.[client],
TARGET.[text] = SOURCE.[text],
TARGET.[cost_center] = SOURCE.[cost_center],
TARGET.[posting_period] = SOURCE.[posting_period],
TARGET.[profit] = SOURCE.[profit],
TARGET.[document_type]= SOURCE.[document_type],
TARGET.[company_code] = SOURCE.[company_code],
TARGET.[Account] = SOURCE.[Account],
TARGET.[Amount_In_Doc_Curr] = SOURCE.[Amount_In_Doc_Curr],
TARGET.[document_currency] = SOURCE.[document_currency],
TARGET.[Amount_In_local_Curr] = SOURCE.[Amount_In_local_Curr],
TARGET.[Amount_In_Grp_Curr] = SOURCE.[Amount_In_Grp_Curr],
TARGET.[group] = SOURCE.[group],
TARGET.[posting_date] = SOURCE.[posting_date],
TARGET.[assignment] = SOURCE.[assignment],
TARGET.[reference] = SOURCE.[reference],
TARGET.[document_header_text] = SOURCE.[document_header_text],
TARGET.[Last_updated_DateTime] = GETDATE(),
TARGET.[HashChecksum] = SOURCE.[HashChecksum]
-- Insert
WHEN NOT MATCHED THEN
INSERT (
[Client],
[text],
[cost_center],
[posting_period],
[profit],
[document_type],
[company_code],
[Account],
[Amount_In_Doc_Curr],
[document_currency],
[Amount_In_local_Curr],
[Amount_In_Grp_Curr],
[group],
[posting_date],
[assignment],
[reference],
[reference_key2],
[reference_key3],
[document_number],
[document_header_text],
[Last_updated_DateTime],
[HashChecksum]
)
VALUES (
SOURCE.[client],
SOURCE.[text],
SOURCE.[cost_center],
SOURCE.[posting_period],
SOURCE.[profit],
SOURCE.[document_type],
SOURCE.[company_code],
SOURCE.[Account],
SOURCE.[Amount_In_Doc_Curr],
SOURCE.[document_currency],
SOURCE.[Amount_In_local_Curr],
SOURCE.[Amount_In_Grp_Curr],
SOURCE.[group],
SOURCE.[posting_date],
SOURCE.[assignment],
SOURCE.[reference],
SOURCE.[reference_key2],
SOURCE.[reference_key3],
SOURCE.[document_number],
SOURCE.[document_header_text],
GETDATE(),
SOURCE.[HashChecksum]
)
OUTPUT $action INTO @SummaryOfChanges;
DECLARE @RowsProcessed INT = 0;
SELECT
@RowsProcessed = ISNULL([INSERT],0) + ISNULL([UPDATE],0) + ISNULL([DELETE],0)
FROM (
SELECT COUNT(*) ChangeCount, Change as ChangeType
FROM @SummaryOfChanges
GROUP BY Change
)Main
PIVOT (
MAX(ChangeCount)
FOR ChangeType IN ([INSERT],[UPDATE],[DELETE])
)Pvt;
SELECT @RowsProcessed AS RowsProcessed;
CREATE OR ALTER TRIGGER [dbo].[tr_merge_personal_expense]
ON personal_expense_staging
AFTER INSERT AS
----- CASE 1: IF (ACCOUNT AND REF_KEY2) ARE MATCHED THEN UPDATE
IF EXISTS
(
SELECT
*
FROM dbo.personal_expense p
INNER JOIN inserted e
ON p.[account] = e.[account] AND p.[reference_key2] = e.
[reference_key2]
)
BEGIN
UPDATE target
SET
target.[document_number] = source.[document_number],
target.[client] = source.[client],
target.[text] = source.[text],
target.[cost_center] = source.[cost_center],
target.[posting_period] = source.[posting_period],
target.[profit] = source.[profit],
target.[document_type] = source.[document_type],
target.[company_code] = source.[company_code],
target.[amount_in_doc_curr] = source.[amount_in_doc_curr],
target.[document_currency] = source.[document_currency],
target.[amount_in_local_curr] = source.[amount_in_local_curr],
target.[amount_in_grp_curr] = source.[amount_in_grp_curr],
target.[group] = source.[group],
target.[posting_date] = source.[posting_date],
target.[assignment] = source.[assignment],
target.[reference] = source.[reference],
target.[reference_key1] = source.[reference_key1],
target.[reference_key3] = source.[reference_key3],
target.[document_header_text] = source.[document_header_text],
target.[last_updated_datetime] = GETDATE()
FROM personal_expense target
INNER JOIN inserted source
ON target.[account] = source.[account]
AND target.[reference_key2] = source.[reference_key2];
END;
------ CASE 2: IF (ACCOUNT AND REF_KEY2) ARE NOT MATCHED THEN INSERT
IF NOT EXISTS
(
SELECT
*
FROM dbo.personal_expense p
INNER JOIN inserted e
ON p.[account] = e.[account] AND p.[reference_key2] = e.
[reference_key2]
)
BEGIN
INSERT INTO dbo.personal_expense
(
[client],
[text],
[cost_center],
[posting_period],
[profit],
[document_type],
[company_code],
[account],
[amount_in_doc_curr],
[document_currency],
[amount_in_local_curr],
[amount_in_grp_curr],
[group],
[posting_date],
[assignment],
[reference],
[reference_key1],
[reference_key2],
[reference_key3],
[document_number],
[document_header_text],
[last_updated_datetime]
)
SELECT
source.[client],
source.[text],
source.[cost_center],
source.[posting_period],
source.[profit],
source.[document_type],
source.[company_code],
source.[account],
source.[amount_in_doc_curr],
source.[document_currency],
source.[amount_in_local_curr],
source.[amount_in_grp_curr],
source.[group],
source.[posting_date],
source.[assignment],
source.[reference],
source.[reference_key1],
source.[reference_key2],
source.[reference_key3],
source.[document_number],
source.[document_header_text],
GETDATE()
FROM inserted source;
END;
SO, you know that of duplicates, merge statement producing an error. Then simply remove the duplicates in your source clause, using DISTINCT:
SELECT DISTINCT *
FROM [dbo].[Personal_Expense_Staging]