delete

Delete records in a batch


WHILE 1 = 1
BEGIN
DELETE TOP(50000) [mySchema].[DimStatic] WITH (XLOCK)
WHERE [PKDimStaticID] NOT IN (SELECT [FKDimStaticID] FROM [#tt])
IF @@rowcount < 1 BREAK; END

Tags: , ,

Friday, March 2nd, 2012 SQL Server No Comments

Delete Dupes using CTE

for my own reference:
delete dupe records, keeping one entry for each

;WITH mycte (PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,myrank)
AS
(
SELECT
PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,
RANK() OVER (PARTITION BY PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId
ORDER BY PKFACTMarketDataId) AS myrank
FROM DCRMCube.FACTMarketData
)

DELETE FROM mycte
WHERE MYRANK >1

Tags: ,

Friday, March 2nd, 2012 SQL Server No Comments