CODEWITHSUNDEEP
sqlsql-servert-sqlmerge
Chicago1988
Chicago1988

Reputation: 684

how to avoid inserting duplicates on a key-less table?

I was asked to convert the below code into a merge. It used to run once a month, and it should run daily now…

Before the below, EmailEvent gets truncated:

DECLARE @upperLimit DATETIME = GETDATE();
DECLARE @lowerLimit DATETIME = DATEADD(day,-1,@upperLimit);

WHILE @upperLimit > '2013-01-31 10:03:09.000'
BEGIN
 INSERT INTO EmailEvent
 Select l.SalesForceID, 
       DENSE_RANK ( ) OVER (PARTITION BY es.email order by sentdate) as [EmailCount],
       es.SentDate,
       esd.eventtype,
       esd.eventdate

 FROM EmailSentDetails esd
 join Silverpop_EmailSent es on esd.emailsent_id = es.EmailSent_ID
 join Leads l on l.Email = es.Email

 WHERE es.SentDate <= @upperLimit AND es.SentDate >= @lowerLimit 


 SET @upperLimit = @lowerLimit;
 SET @lowerLimit = DATEADD(day,-1, @lowerLimit);

END

I know MERGE can’t be done, because there is no key on EmailEvent (no way to identify a row uniquely!)

The only thing I can think of is, instead of truncating/inserting, just adding the below, will it improve it? is it a valid way of avoind a truncate/insert?

WHERE Not exists (select 1 from EmailEvent ev 
                  where ev.salsforceId=l.SalesForceID 
                  and ev.emailcount = DENSE_RANK ()OVER(PARTITION BY es.email order by sentdate) 
                  and ev.sentDate=es.SentDate 
                  and ev.EventDate=esd.EventDate)

Upvotes: 0

Views: 51

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

You could do a MERGE on all the columns in the table.

But that would be overkill, since you'd never do any updates with the MERGE.

All you really need to do is an INSERT .. WHERE NOT EXISTS() that checks all the columns in the table, just as you suggested in your question. That's how I'd do it.

Upvotes: 1

cloudsafe
cloudsafe

Reputation: 2504

Put a unique index on the column(s) you want to avoid having duplicates and use IGNORE_DUP_KEY. This ignores attempts to add duplicates, allowing inserts to continue:

CREATE UNIQUE NONCLUSTERED INDEX [IX_AvoidDuplicateEntries] ON [dbo].[MyTable]
(
    [MyColumn1] ASC --, [MyColumn2] ASC, [MyColumn3] ASC etc.
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Upvotes: 0

Related Questions