Solved

SQL Data Migration - Duplicate Keys found

Posted on 2012-03-22
3
542 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:mossmis
3 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 37754747
run this on your oracle:

select count(*), upper(serial)
from <table>
having (count(*) > 1)
group by upper(serial)


That will at least find your duplicates.  Then in your export you can uppercase everything
if you're not supposed to have lower case characters.


also you can set the coalation on your SQL DB so that it considers case same as oracle.
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37754753
If you want to check them in Oracle,
try:

SELECT Serial_Number
FROM YourTable
WHERE REGEXP_LIKE(Serial_Number ,'[:lower:]');
0
 

Author Closing Comment

by:mossmis
ID: 37754807
Perfect! Short and sweet! I found 15 dups! Now time to yell at people for making mistakes!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question