Import Data and Ignore Duplicates in SQL Server

Question

I'm using SQL Server 2008 R2. I have a source table of data (I_Vendor) that may have duplicates on the CompanyName column. I want to import that data into a new table (Vendor) but the new table has a Name column (that corresponds to CompanyName) with a unique constraint on it. It's been a while since I've done SQL but I saw the MERGE function, and it appears it fits the bill. I wrote the following:

MERGE Vendor AS T
USING I_Vendor AS S
ON (T.Name = S.CompanyName) 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(VendorId, Name, ContactName, ContactInfoId) 
        VALUES(S.Vendor_ID, S.CompanyName, S.ContactName, S.Vendor_ID+10000);

It generates a "Violation of UNIQUE KEY constraint" and gives the name of the unique constraint on Vendor.Name. Anybody know what I'm doing wrong?

if in the source table there are two rows with same companyName and different contactname which contact name do you want in the Vendor table? similarly what about other columns? — Aditya Naidu
– Aditya Naidu, Commented Nov 4, 2011 at 0:48
Sorry for not being clearer. The ContactName doesn't really matter, I just need to get a clean list of all the Vendor names (plus I want to figure out how to use the Merge command). — Tod
– Tod, Commented Nov 4, 2011 at 5:14

srgerg · Accepted Answer · 2011-11-04 03:04:27Z

1

Your MERGE statement will insert all rows from I_Vendor that do not have a matching row in the Vendor table.

For example, suppose there are two rows in the I_Vendor table with company name "X", and further suppose that company name "X" does not appear in the Vendor table, then both rows will be inserted into the Vendor table, violating the constraint.

To fix the problem, you need to ensure that there is only one row per company name in the source data of the MERGE statement. The following merge statement does this, but as Aditya Naidu has already pointed out, we don't know what you want to do when there multiple records with the same company name in the I_Vendor table:

MERGE Vendor AS T
USING (SELECT MAX(Vendor_ID), CompanyName, MAX(ContactName), MAX(ContactInfoId)
       FROM I_Vendor
       GROUP BY CompanyName) AS S
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(VendorId, Name, ContactName, ContactInfoId) 
         VALUES(S.Vendor_ID, S.CompanyName, S.ContactName, S.Vendor_ID+10000);

answered Nov 4, 2011 at 3:04

srgerg

19.4k4 gold badges59 silver badges40 bronze badges

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

1 Comment

Tod Over a year ago

Thanks for the clear explanation and the working example with the MERGE command. I was thinking once the first x contact name came in the second x would not match. I've been away from SQL for too long and I forgot how to think in a set oriented way.

Collectives™ on Stack Overflow

Import Data and Ignore Duplicates in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related