Page Comparison

Zasio: Duplication check and clean-up

I have several T-SQL queries that I use to do database clean-up work.
All are located here:

UL-Users/robe9688/JAZZHANDS/SQL queries general/housekeeping

I start with this query:
!dupe_itemid.sql

select COUNT(1) as COUNT, REC_UDF_ITEM_ID AS ItemID from dbo.RECORDS

WHERE (REC_BARCODE is not null) and (REC_UDF_ITEM_ID <> 'DESTROYED')

GROUP BY REC_UDF_ITEM_ID

HAVING COUNT(1) > 1;

If the list from the above query is extensive, I do a step through of this query:
!delete_active_not_ingested.sql query to isolate any duplicate records that haven't been boxed and shelved and then delete them
Anchor_GoBack_GoBack

select COUNT(1) as COUNT, REC_UDF_ITEM_ID AS ItemID
INTO DUPES
from dbo.RECORDS
WHERE (REC_BARCODE is not null) and (REC_UDF_ITEM_ID <> 'DESTROYED')
GROUP BY REC_UDF_ITEM_ID
HAVING COUNT(1) > 1;

SELECT DISTINCT REC_BARCODE as BARCODE, REC_STATE, ItemID, REC_DT_CREATION
INTO DUPES_BARCODES
FROM DBO.RECORDS
RIGHT JOIN DBO.DUPES
ON REC_UDF_ITEM_ID=[ItemID]
where REC_STATE <> 'I'

SELECT REC_BARCODE, REC_UDF_ITEM_ID, REC_BOX_NO
FROM dbo.RECORDS AS B
JOIN dbo.DUPES_BARCODES AS D
ON REC_BARCODE = BARCODE

DELETE FROM B
FROM dbo.RECORDS as B
JOIN dbo.DUPES_BARCODES as D
ON REC_BARCODE = BARCODE

DROP TABLE DUPES

DROP TABLE DUPES_BARCODES