how to get duplicate values

Question

0.00/5 (No votes)

See more:

Hi All,

We have same structure master table and a staging table with 30 columns, we are trying to load the data from staging to master table.In master table have 6 primary key columns but while loading the data it show primary key violation error.How to identify the duplicate records.

Thanks,
Ramesh

Posted 19-Apr-13 7:51am

ramesh4128

Add a Solution

Comments

Maciej Los 19-Apr-13 14:54pm

Please, be more specific and provide more details (example data).

gvprabu 20-Apr-13 4:35am

Give the table Structure and data... then only we will try to solve

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ManojABhole · Answer 1 · 2013-06-07T00:02:00

SQL

SELECT * INTO temp_duplicates FROM tbl_master

INSERT INTO temp_duplicates
SELECT * FROM tbl_staging

--to identify duplicates
SELECT id,name,COUNT(*) 
FROM temp_duplicates
GROUP BY id,name --pk columns in master
HAVING COUNT(*)>1
GO

--insert non duplicate records
INSERT INTO tbl_master
SELECT * FROM temp_duplciates 
WHERE NOT EXISTS(SELECT 1 FROM tbl_master WHERE tbl_master.id = temp_duplciates.id AND tbl_master.name = temp_duplciates.name)

DROP TABLE duplicates

[Edit]Code block added[/Edit]

OriginalGriff · Answer 2 · 2013-04-19T08:05:00

Solution 1

Try:

SQL

SELECT m.* FROM Master m JOIN Staging s ON m.Id = s.Id

Posted 19-Apr-13 8:05am

OriginalGriff

Comments

ramesh4128 19-Apr-13 14:08pm

while loading only it get error and i have almost 6 key column in master table and Staging table doesn't have any key column

OriginalGriff 19-Apr-13 14:25pm

So list your primary key columns instead of the one I showed...

Maciej Los · Answer 3 · 2013-06-05T11:05:00

First of all, try to delete primary key (PK) one by one. When you remove first PK, try to use SELECT statement on your table to check for errors. If it wasn't helpful, remove next PK, until success.

Warning: Please, be careful. Removing PK's, you can lose integrity of the data!!!

When you successfully fetch data, try to restore PK's and - also one by one - again use SELECT statement. By doing in this way, you can find the column which raises error ;)

I can't give you an example query, because you did not provide enough information ;(

SamRST · Answer 4 · 2013-06-05T21:02:00

Solution 3

You can take distinct of primary keys & its count.
So if count is more than 1. Then its duplicate.
Good luck.

Posted 5-Jun-13 21:02pm

SamRST

how to get duplicate values

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0