Remove duplicates from Temp Table

[snufse]

Have following temp table populated with data:

 #TempTable
        
            petroex_ind char(1)
            petroex_ver char(2)
            record_id char(1)
            supplier_code char(3)     
            division char(3)
            site_id char(1)
            splc_code char(9) 
            terminal_no char(9)
            period char(5)
            movement_month char(2)
            movement_year char(4)
            movement_date char(8)
            trans_descr char(40)
            ticket_no char(16)
            petroex_code char(3)
            product_code char(8),
            tank_code char(15) 
            qty_gross char(11)     
            qty_net char(11) 
            cred_flag char(1) 
            uom char(1)
            mode_transp char(2)                           
            mode_transp_descr char(15)
            scac char(5) 
            fein char(9)
            suffix char(1) 

I need to loop through the table a remove duplicate records where

ticket_no, petroex_code, tank_code, qty_gross and qty_net are the same.

Is that possible?

[skhanal]

How do you decide which one to keep?

[snufse]

That is a good question. What I did was

select distinct *
into #TempTable1
from #TempTable

and that solved my problems.

Thank you.

[skhanal]

This will remove duplicates where all columns are same not just the ones you listed before.

[mikr0s]

;WITH dedup
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY ticket_no, petroex_code, tank_code, qty_gross, qty_net ORDER BY ticket_no /*place tie breaker fields here*/) AS rownum
FROM #temptable
)
DELETE FROM dedup
WHERE rownum > 1

--HTH--