November 8, 2015 at 8:13 am
Hi,
I am new to use MERGE statement. The MERGE does not find any match and insert row into an existing row on the target table. The CardNumber is a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. Any suggestion?
TRUNCATE TABLE [dbo].[GiftCard]
MERGE INTO [dbo].[GiftCard] as target
USING
(SELECT g.[CardNumber]
,YEAR(g.[Date])
,MONTH(g.[Date])
,f.[Period]
,g.[TransAmount]
,g.[BalanceAmount]
FROM GiftCard_Transaction as g
INNER JOIN Fiscal_Calendar as f
ON g.[Date] = f.[Date]
WHERE (g.[TransType] = 'I' OR g.[TransType] = 'F' OR g.[TransType] = 'T' )
AND g.[Void] = 'N'
) as source ( [CardNumber],[Date], [Month], [Period], [TransAmount], [BalanceAmount])
ON (target.[CardNumber] = source.[CardNumber])
WHEN MATCHED THEN
UPDATE
SET target.[Year] = source.[Date]
,target.[Month] = source.[Month]
,target.[Period] = source.[Period]
,target.[TransAmount] = target.[TransAmount]
+ CAST(source.[TransAmount] as decimal(10,2))
,target.[BalanceAmount] = CAST(source.[BalanceAmount] as decimal(10,2))
WHEN NOT MATCHED THEN
INSERT
(
[CardNumber]
,[Year]
,[Month]
,[Period]
,[TransAmount]
,[BalanceAmount]
)
VALUES
(
source.[CardNumber]
,source.[Date]
,source.[Month]
,source.[Period]
,CAST(source.[TransAmount] as decimal(10,2))
,CAST(source.[BalanceAmount] as decimal(10,2))
);
November 8, 2015 at 9:09 am
Can you post the DDL (create table script) for both tables?
😎
November 8, 2015 at 9:16 am
Here is the GiftCard Table:
CREATE TABLE [dbo].[GiftCard](
[CardNumber] [varchar](50) NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Period] [int] NULL,
[TransAmount] [decimal](10, 2) NULL,
[BalanceAmount] [decimal](10, 0) NULL,
CONSTRAINT [PK_GiftCard] PRIMARY KEY CLUSTERED
(
[CardNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Here is the GiftCard_Transaction Table:
CREATE TABLE [dbo].[GiftCard_Transaction](
[BM] [varchar](50) NULL,
[CardNumber] [varchar](50) NULL,
[EGC] [varchar](50) NULL,
[Date] [varchar](50) NULL,
[Datetime] [varchar](50) NULL,
[TransAmount] [varchar](50) NULL,
[BalanceAmount] [varchar](50) NULL,
[TransType] [varchar](50) NULL,
[Void] [varchar](50) NULL,
[Reversal] [varchar](50) NULL,
[AuthCode] [varchar](50) NULL,
[Store] [varchar](50) NULL,
[OpID] [varchar](50) NULL,
[Issuance Year] [smallint] NULL,
[Issuance Month] [smallint] NULL,
[Redemption Year] [smallint] NULL,
[Redemption Month] [smallint] NULL
) ON [PRIMARY]
GO
November 8, 2015 at 9:31 am
I am getting below error:
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (6033590000063027768).
The statement has been terminated.
November 8, 2015 at 9:56 am
Quick question, would there be duplicate CardNumber in the dbo.GiftCard table?
😎
Slight alteration to the query
MERGE [dbo].[GiftCard] as target
USING
(SELECT DISTINCT
g.[CardNumber]
,YEAR(g.[Date])
,MONTH(g.[Date])
,f.[Period]
,g.[TransAmount]
,g.[BalanceAmount]
FROM GiftCard_Transaction g
INNER JOIN Fiscal_Calendar f
ON g.[Date] = f.[Date]
WHERE g.[TransType] IN ('I','F','T' )
AND g.[Void] = 'N'
) as source ( [CardNumber],[Date], [Month], [Period], [TransAmount], [BalanceAmount])
ON (target.[CardNumber] = source.[CardNumber])
WHEN MATCHED THEN
UPDATE
SET
target.[Year] = source.[Date]
,target.[Month] = source.[Month]
,target.[Period] = source.[Period]
,target.[TransAmount] = target.[TransAmount]
+ CAST(source.[TransAmount] as decimal(10,2))
,target.[BalanceAmount] = CAST(source.[BalanceAmount] as decimal(10,2))
WHEN NOT MATCHED THEN
INSERT
(
[CardNumber]
,[Year]
,[Month]
,[Period]
,[TransAmount]
,[BalanceAmount]
)
VALUES
(
source.[CardNumber]
,source.[Date]
,source.[Month]
,source.[Period]
,CAST(source.[TransAmount] as decimal(10,2))
,CAST(source.[BalanceAmount] as decimal(10,2))
);
Alternative dedup of the source
MERGE [dbo].[GiftCard] as target
USING
(SELECT DISTINCT
ROW_NUMBER() OVER
(
PARTITION BY g.[CardNumber]
ORDER BY g.[Date] DESC
) AS GC_RID
,g.[CardNumber]
,YEAR(g.[Date])
,MONTH(g.[Date])
,f.[Period]
,g.[TransAmount]
,g.[BalanceAmount]
FROM GiftCard_Transaction g
INNER JOIN Fiscal_Calendar f
ON g.[Date] = f.[Date]
WHERE g.[TransType] IN ('I','F','T' )
AND g.[Void] = 'N'
) as source (GC_RID, [CardNumber],[Date], [Month], [Period], [TransAmount], [BalanceAmount])
ON
target.[CardNumber] = source.[CardNumber]
AND source.GC_RID = 1
WHEN MATCHED THEN
UPDATE
SET
target.[Year] = source.[Date]
,target.[Month] = source.[Month]
,target.[Period] = source.[Period]
,target.[TransAmount] = target.[TransAmount]
+ CAST(source.[TransAmount] as decimal(10,2))
,target.[BalanceAmount] = CAST(source.[BalanceAmount] as decimal(10,2))
WHEN NOT MATCHED THEN
INSERT
(
[CardNumber]
,[Year]
,[Month]
,[Period]
,[TransAmount]
,[BalanceAmount]
)
VALUES
(
source.[CardNumber]
,source.[Date]
,source.[Month]
,source.[Period]
,CAST(source.[TransAmount] as decimal(10,2))
,CAST(source.[BalanceAmount] as decimal(10,2))
);
November 8, 2015 at 10:02 am
The target GiftCard table has the cardnumber as the primary key which does not allow duplicate. The source table GiftCard_Transaction will have duplicate data because it is transactional table where people can redeem more than one time with the same card. This is why I need to update the GiftCard table otherwise build the GiftCard table by insert a new GiftCard.
November 8, 2015 at 10:03 am
I cannot use distinct because we want to capture all giftcard transactions...
November 8, 2015 at 10:09 am
Brian_Ho (11/8/2015)
I cannot use distinct because we want to capture all giftcard transactions...
Then the primary key is wrong as there will be many transactions on the same key, you will need to amend the key to allow for multiple transactions. As it is, subsequent transactions will overwrite the previous ones.
😎
November 8, 2015 at 10:22 am
The target table GiftCard should have a unique CardNumber because it keeps track of the ending balance. The source table GiftCard_Transaction will have gift card redemption transaction from the same card... For example:
Initial gift card activated with $100. The GiftCard_Transaction will have $100 activation come into the system. This job will insert the initial gift card activation in the GiftCard table. Now, the customer start to redeem the gift card with $50 purchase on the second day. There will be a $50 redemption in the GiftCard_Transaction table that I will need to update the GiftCard table with the ending balance of $50 since the customer redeem $50 purchase. On day three, the same customer uses the same gift card to redeem another $25 purchase. There will be a third gift card transaction in the GiftCard_Transaction table with another $25 purchase to update the master GiftCard table. The ending balance on the master GiftCard table will end with $25 ending balance. Notice that there could be multiple redemption transactions in the GiftCard_Transaction table on the same day depending on how many redemptions the customer had.
Thanks,
November 8, 2015 at 12:34 pm
What other alternative that you recommend? How can we change this query from MERGE to IF cardnumber EXISTS update ELSE insert method?
November 8, 2015 at 1:11 pm
Brian_Ho (11/8/2015)
What other alternative that you recommend? How can we change this query from MERGE to IF cardnumber EXISTS update ELSE insert method?
The design is flawed and you will need to split the card table, it should not hold any transactional details. I suggest you abandon the MERGE approach, gift card transaction table should have both debit and credit transaction and the rest is just a query that calculates the balance.
😎
November 9, 2015 at 5:32 am
it looks to me that you are merging into GiftCard, and not GiftCard_Transactions. the balance does not belong int he Gift Card table.
there's only one card, heck i'd think it was prepopulated or populated whenever a run of cards are created....they may be activiated later at a register at a grocery store for example.
the detail transactions should be inserted against your other table, and a view should be used to calculate the current balance; don't store the balance in a table, i think it's better to calculate it from the details
Lowell
November 9, 2015 at 5:59 am
Maybe some aggregation is needed.
MERGE [dbo].[GiftCard] as target
USING
(SELECT
g.[CardNumber]
,YEAR(MAX(g.[Date]))
,MONTH(MAX(g.[Date]))
,MAX(f.[Period])
,SUM( g.[TransAmount])
,SUM( g.[BalanceAmount])
FROM GiftCard_Transaction g
INNER JOIN Fiscal_Calendar f
ON g.[Date] = f.[Date]
WHERE g.[TransType] IN ('I','F','T' )
AND g.[Void] = 'N'
GROUP BY g.[CardNumber]
) as source ( [CardNumber],[Date], [Month], [Period], [TransAmount], [BalanceAmount])
ON (target.[CardNumber] = source.[CardNumber])
WHEN MATCHED THEN
UPDATE
SET
target.[Year] = source.[Date]
,target.[Month] = source.[Month]
,target.[Period] = source.[Period]
,target.[TransAmount] = target.[TransAmount]
+ CAST(source.[TransAmount] as decimal(10,2))
,target.[BalanceAmount] = CAST(source.[BalanceAmount] as decimal(10,2))
WHEN NOT MATCHED THEN
INSERT
(
[CardNumber]
,[Year]
,[Month]
,[Period]
,[TransAmount]
,[BalanceAmount]
)
VALUES
(
source.[CardNumber]
,source.[Date]
,source.[Month]
,source.[Period]
,CAST(source.[TransAmount] as decimal(10,2))
,CAST(source.[BalanceAmount] as decimal(10,2))
);
November 9, 2015 at 6:36 am
Ding Ding Ding! We HAVE a winner! And I'm not just saying it 'cause our names sound alike.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply