Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
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 8:51am
Comments
Maciej Los at 19-Apr-13 14:54pm
   
Please, be more specific and provide more details (example data).
gvprabu at 20-Apr-13 4:35am
   
Give the table Structure and data... then only we will try to solve
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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]
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try:
SELECT m.* FROM Master m JOIN Staging s ON m.Id = s.Id
  Permalink  
Comments
ramesh4128 at 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 at 19-Apr-13 14:25pm
   
So list your primary key columns instead of the one I showed...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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 Wink | ;)
 
I can't give you an example query, because you did not provide enough information ;(
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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

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

  Print Answers RSS
0 OriginalGriff 562
1 Sergey Alexandrovich Kryukov 484
2 Maciej Los 325
3 DamithSL 233
4 Mathew Soji 195
0 OriginalGriff 7,168
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,461
3 Manas Bhardwaj 4,876
4 Maciej Los 4,450


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 7 Jun 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100