Avoid duplicates on INSERT INTO SELECT query in SQL Server

Question

How can I avoid duplicate using the provided below query

INSERT INTO dbo.Entities(EntityId, [Name], [Description], [Type], Source)
    SELECT DISTINCT 
        CUST_CODE, NAME, FULLDESCRIPTION, 'Agency' AS Type, 'SunDbAgencies' AS Source
    FROM dbo.VW_SUNDB_AGENCIES

I've already tried all the answers here :

Avoid duplicates in INSERT INTO SELECT query in SQL Server to no avail.

The duplicates are in the dbo.VW_SUNDB_AGENCIES TABLES not the INSERT table, So I gather I need a way to remove duplicate from the select before inserting

Here is one of the duplicates which is why a simple distinct doesn't work:

Gordon Linoff · Accepted Answer · 2019-11-20 21:35:04Z

2

The problem is that select distinct is not sufficient. You still have duplicates in the underlying table, but with different names or descriptions.

I view this as a problem. But, you can work around it by selecting one arbitrary row per cust_code, using row_number():

insert into dbo.Entities (EntityId, [Name], [Description], [Type], Source)
  select CUST_CODE, NAME, FULLDESCRIPTION, 'Agency' AS [Type], 'SunDbAgencies' AS Source
  from (select a.*,
               row_number() over (partitoin by cust_code order by cust_code) as seqnum
        from dbo.VW_SUNDB_AGENCIES a
       ) a
  where seqnum = 1 and
        not exists (select 1 from dbo.Entities E where A.CUST_CODE = E.EntityId);

answered Nov 20, 2019 at 21:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jesse Over a year ago

The view is definitely a problem , but I can't change it. The provided script works perfectly. Thanks

ASH Over a year ago

I just faced the same problem a couple days ago. I love the 'Where rownumber = 1' solution. Very, very, very elegant.

Collectives™ on Stack Overflow

Avoid duplicates on INSERT INTO SELECT query in SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related