I'm relatively new to T-SQL and am having some trouble getting my import MERGE stored procedure to run. I'm sure it's a simple mistake but I've been reading article after article trying to find my mistake.
I'll give you all an overview of what it is I'm doing (or trying to do) so that it all makes sense. The purpose of this application is to view a list of members that have received a marketing email and recently purchased a service/product based on that email.
Lets call this application "MktRep" which was created in C# MVC 5 - ASP.NET using Visual Studios 2017 community edition. For the DB, I'm using SQL Server Management Studios 2014. Continuing on.
MktRep has two main views/pages (which actually have multiple partial views):
- One page to import data
- One page to view the data
Ok so let's continue. Now, when the user imports data, they provide the following information:
- CSV file with the account numbers of the members
- The date the mailer was sent
- The type of mailer that was sent (we have 2 different types currently)
Once the CSV file is imported, we convert that into XML format, then pass that data along with the other collected data, to the database to INSERT/UPDATE/MERGE via stored procedure.
Now from the database side, we have multiple tables (and databases) that are providing specific account details and have a new table for all of the imported data.
Originally, I was taking the imported values, comparing the account number, product booked date, and mailed date and storing the matches in this new table. However, I realized this was a bad idea because if the member booked/purchased a service after the upload date, that data would be lost and there could potentially be discrepancies. So, I wait to do the comparison until the "View Report" page.
So here's where I'm having issues.
When we import account data, we DO NOT want to have duplicate records. However, we DO want to update the letter type and mailed date IF the record already exists.
After reading a TON of different articles about using UPDATE/INSERT, MERGE, and everything in between, I decided I would try using MERGE.
Currently, whenever there is more than 1000 records in the XML, it takes almost a minute for the query to run. Any ideas on how to trim down that time?
For the sake of testing the code, I'm running it as a regular SQL query but the variables defined at the top would normally be the variables passed from the web app.
SQL Code:
BEGIN
DECLARE
@maildate date = '04-01-2017',
@lettertype varchar(10) = 'ALR',
@xmldata varchar(max) = '<data>
<rec><mem> 400117788 </mem></rec>
<rec><mem> 122244455 </mem></rec>
<rec><mem> 188642145 </mem></rec>
<rec><mem> 348865442 </mem></rec>
<rec><mem> 199754621 </mem></rec>
<rec><mem> 955421234 </mem></rec>
<rec><mem> 754136845 </mem></rec>
<rec><mem> 946612164 </mem></rec>
<rec><mem> 102446785 </mem></rec>
<rec><mem> 478310246 </mem></rec></data>'
/*----------------------------*/
/*-- GET MEM DATA FROM XML --*/
/*----------------------------*/
DECLARE @memData TABLE
(
mem varchar(9)
)
DECLARE @num int
EXEC sp_xml_preparedocument @num OUTPUT, @xmldata
INSERT INTO @memData
SELECT * FROM OPENXML (@num, '/data/rec',2)
WITH (mem varchar(9))
EXEC sp_xml_removedocument @num
/*-------------------------------*/
/*-- INSERT DATA INTO TEMP TBL --*/
/*-------------------------------*/
DECLARE @tempData TABLE
(
account varchar(10),
mem varchar(9),
maildate date,
lettertype varchar(5)
)
INSERT into @tempData (account, mem, lettertype, maildate)
SELECT
s.account, s.mem, @lettertype, @maildate
FROM
[tsqldb\sqldb].sumdb.dbo.tblShareFile s
INNER JOIN [tsqldb\sqldb].sumdb.dbo.tblServiceFile l
ON l.account = s.account
INNER JOIN @memData d
ON d.mem = s.mem
WHERE
s.AccFlag != 80
AND s.AccType = '0'
/*----------------------------------*/
/*-- CHECK FOR DUPS & INSERT DATA --*/
/*----------------------------------*/
MERGE INTO dbo.tblMktRep AS TARGET
USING @tempData AS source
ON source.account = target.Account
WHEN NOT MATCHED BY TARGET THEN
INSERT (Account, mem, lettertype, MailedDate)
VALUES (source.account, source.mem, source.lettertype, source.maildate)
WHEN MATCHED AND (target.MailedDate < source.maildate) THEN
UPDATE
SET
target.lettertype = source.lettertype,
target.MailedDate = source.maildate;
UPDATE: I figured out the error I was getting was because of the "BEGIN". Once I removed that, it ran fine. Now the only issue is why it's taking so long to run.
UPDATE: Now I'm currently getting the following error:
Msg 8672, Level 16, State 1, Line 67
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. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
I've read other posts and articles but still didn't quite find a solution:
