mossmis
asked on
SQL Data Migration - Duplicate Keys found
I have a massive issue I could use help with, one that deserves more than 500 points I am allowed to award.
I have an Oracle Table in a 9i database that houses my company's Serial Number Data (8million records + with 3,000 per day being added). The Serial Number field in this table is the primary key. My company is migrating from an Oracle Database to a SQL Database. I have recreated the table in SQL successfully. When I do a test export than import into SQL, I get a "Duplicate Key" Error and the load fails.
From what I understand, Oracle database distingushes from uppper and lower case characters. The SQL server does not. So what I think is happening is that I might have the following example in my Oracle DB:
Serial_Number
a123
A123
ORacle considers them different and when I import them into SQL, SQL consider's it duplicate. Serial Numbrs in my system are not supposed to be duplicate. They are user entry problems. I need to be able to find these duplicates some how and fix them.
Does anyone have an idea to have I can find serial numbers with lower case characters?Our system should only have upper case.
Thanks in advance.
I have an Oracle Table in a 9i database that houses my company's Serial Number Data (8million records + with 3,000 per day being added). The Serial Number field in this table is the primary key. My company is migrating from an Oracle Database to a SQL Database. I have recreated the table in SQL successfully. When I do a test export than import into SQL, I get a "Duplicate Key" Error and the load fails.
From what I understand, Oracle database distingushes from uppper and lower case characters. The SQL server does not. So what I think is happening is that I might have the following example in my Oracle DB:
Serial_Number
a123
A123
ORacle considers them different and when I import them into SQL, SQL consider's it duplicate. Serial Numbrs in my system are not supposed to be duplicate. They are user entry problems. I need to be able to find these duplicates some how and fix them.
Does anyone have an idea to have I can find serial numbers with lower case characters?Our system should only have upper case.
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Short and sweet! I found 15 dups! Now time to yell at people for making mistakes!
try:
SELECT Serial_Number
FROM YourTable
WHERE REGEXP_LIKE(Serial_Number ,'[:lower:]');