removing duplicate records

[Anthony97]

I'm working on removing duplicate records from a table in sql.
I've tried using select distinct, and I've grouped by but I'm still running into some issues. Attached is a sample of the output table.

[Delerna]

and the query looks like?

Also, when you say you are trying to remove duplicate records from a table...
do you mean

1)you want to actually delete the extra records from the table?
Or do you mean
2) you want a query that dosn't display the duplicates but leaves them in the table?

Reason I ask is because your question suggests to me that you want the first option but you are using a query that does the second option and then wondering why the duplicates are still in the table

[ssnaik84]

you mean duplicate columns are having same "Load Id" and "Shipment Id" values?

there is no other unique id to identify duplicates.. for delete operation..
Can you add an extra auto increment column into table, by which we can separate out duplicate records and delete them..

[Anthony97]

Delerna,

Yes I would like to delete the extra records from the table, that's exactly what I'm looking to do. Here's the code I tried to use but it removes too much, I found it while browsing the internet, it was borken down into 4 parts and does not do what I really want it to do.


--part 1

select * from tblChargeAmts


--part2

with temptable as
(
select ROW_NUMBER() over (PARTITION BY [Load Id], [Ship Id] ORDER BY [Pc Code]) As rownumber,*
FROM tblChargeAmts
)
Select * FROM temptable


--part 3

with temptable as
(
select ROW_NUMBER() over (PARTITION BY [Load Id], [Ship Id] ORDER BY [Pc Code]) As rownumber,*
FROM tblChargeAmts
)
delete from temptable where rownumber > 1

--part4

select * from tblChargeAmts

[Delerna]

Yes that query will completely remove all rows that are duplicated.
You don't want that, you want to leave just one of the records but remove the extra's, right.
You can't delete the duplicates manually because SQL Server won't let you.

There are many ways to achieve that.
1) add an auto number column to the table and either
write a query to delete each duplicate that has the highest auto number.
or

delete them manually

Once all duplicates are gone you can remove the new field

2) Create a copy of the table and delete all records from it(the copy).
Then do

    INSERT INTO TheCopyTable
    SELECT distinct * from TheOriginalTable
 

Then you could delete TheOriginalTable and rename TheCopyTable
Or
Delete all records in TheOriginalTable and reinsert them from the TheCopyTable and then delete TheCopyTable

    INSERT INTO TheCopyTable
    SELECT distinct * from TheOriginalTable
 
    DELETE FROM TheOriginalTable
 
    INSERT INTO TheOriginalTable
    SELECT * from TheCopyTable
 
    DROP TABLE TheCopyTable
 

3) Similar to 2 but into a temporary table instead of a copy table
Then delete all records from TheOriginalTable and reinsert them back
from the temp table

4).... Use your imagination

5).... There are lots of possibilities

6) It all depends on your particular situation

If this is a one off task then I suggest that Option 2 may be easiest for you.


NOTE
You really should make "Load Id" and "Shipment Id" key fields so that
it will be impossible for users to create duplicate records in the future.

[kabaari]

Delerna,

I have been trying to accomplish the same thing for a while, thanks. But, I import data from AS400 to SQL Server and my problem is importing duplicates and the fact that I have no PK in that table. I want to import only distinct values.

Thanks,

DJ Khalif

[ck9663]

You're going to have to import it first into a working table and run something to update your SQL Server main table.

Happy Coding!!!

--- CK

[Delerna]

I also, routinely do migration of data from an ERP software called M3 (or MoveX) on an AS400 to SQLServer via DTS.

I presume you are using DTS to import your data from an AS400 into SQLServer.
If that is the case I don't really understand the issue?
Why wouldn't you just do a

SELECT DISTINCT in the DTS query that is selecting the data on the AS400.
That should stop the duplicates getting into the SQLServer Table in the first place!

It also raises some questions.
In the DTS the query that is selecting data from the AS400.
Are you joining tables?
If so, do you not have enough condions in one of the WHERE clauses,
and that is why you are getting duplicate records imported to SQL server.


PS
In my posts, as with all experts here, I am just trying to be helpful.
Not knowing your experience level, I may say/ask something that you have tried/know already.