duplicate primary key sql

Question

This will seem to be a duplicate of SQL Server : Duplicate Primary Key error.

I will give some background. First, I am using SQL Express. Second, I am using the import and export wizard. Third, the source file is a text file. Fourth, I have successfully imported two other iterations of this file, FIRMS. 2017q2 and 2013q2 imported fine but 2013q1 will not. The reason is a duplicate primary key error.

My primary keys are state, account, unit, and yrqtr. There are 25 other foreign keys. Sample data is below.

The error message produces an offending value, so to speak. I find that value in excel and in notepad ++. However, when I dig I find that the combination of those four only happens once.

As a further test, In excel I concatenate the four primary key values into one field and then search for duplicates in the concatenated (combined) value. Excel turns up results but when you look at them, they are not the same. The unit number differs in each item considered a duplicate. I notice that when name1 (not a primary key) has more than 1 value, excel (and SQL I presume) consider it a duplicate. I am only using excel as a tool to find the duplicates.

state   area    account    unit     yrqtr     name1     name2
  32   000001    12345    00001     201301    bakery    xyz bakery
  32   000003    12346    00001     201301    auto      xyz auto

As for SQL, I imported 201301 as a load table of sorts instead of appending it to FIRMS. From there, I used the following code. It turns up no results. Lastly, I have looked at 201702 and 201302 and found no duplicates

select state,account,unit,yrqtr
from dbo.201301
Group by state,account,unit,yrqtr
having count(*) >1

So any ideas as to how I solve this issue of seemingly nonexistent duplicates?

Answer 1

Take an empty copy of the destination table, disable the primary and foreign keys, import all the files into that table and run a query to check for duplicated data. That might reveal if the data is being modified during import.

SELECT state, account, unit, yrqtr, COUNT(*) AS dups 
FROM TableCopy 
GROUP BY state, account, unit, yrqtr 
HAVING COUNT(*) > 1