Czasami się zdarza że w tabeli jest sporo takich samych wiersz które niepotrzebnie zaśmiecają tabelę, a czasami powodują „błędy” podczas odczytu danych. Dziś w pracy się spotkałem z taką tabelą gdzie przez pomyłkę został uruchomiony skrypt z insertami do tabeli wrzucający kilkaset wierszy. Dziś pokażę jak w prosty sposób ich się pozbyć pozostawiając unikalne niezdublowane wiersze.
Na początek stworzymy sobie prostą tabelę zawierającą trzy kolumny.
USE [Database1]
GO
CREATE TABLE [dbo].[duplikaty](
[randomID] [int] NULL,
[nazwa] [nchar](10) NULL,
[region] [nchar](10) NULL
) ON [PRIMARY];
GO
W pierwszej kolumnie będzie wpisana losowa liczba w zakresie 0-10. W drugiej i trzeciej kolumnie będzie string połączony w losową liczbą od 1 do 10. A więc napełniamy tabelę 10.000 wierszami
DECLARE @intCounter as INT = 1;
DECLARE @naz1 as INT = 1;
DECLARE @reg1 as INT = 1;
WHILE @intCounter <= 10000
BEGIN
SET @naz1 = ROUND(rand()*10,0);
SET @reg1 = ROUND(rand()*10,0);
--PRINT 'Bieżąca wartość licznika to: '+ CAST(@intCounter AS VARCHAR);
INSERT INTO [dbo].[duplikaty]([randomID],[nazwa],[region])
VALUES (ROUND(rand()*10,0),'nazwa'+CAST(@naz1 as nvarchar(10)),'region'+CAST(@reg1 as nvarchar(10)))
SET @IntCounter = @IntCounter + 1;
END;
Sprawdzamy czy dane się zapisały
select * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;
randomID nazwa region ----------- ---------- ---------- 0 nazwa0 region3 0 nazwa0 region7 0 nazwa0 region0 0 nazwa0 region4 ... 10 nazwa9 region3 10 nazwa9 region6 10 nazwa9 region8 10 nazwa9 region4 (10000 rows affected)
W tabeli jest 10.000 wierszy ale z pewnością wiele wierszy się powtarza. Sprawdzamy ile jest unikalnych wierszy
select DISTINCT * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;
randomID nazwa region ----------- ---------- ---------- 0 nazwa0 region3 0 nazwa0 region7 0 nazwa0 region0 0 nazwa0 region4 ... 10 nazwa9 region3 10 nazwa9 region6 10 nazwa9 region8 10 nazwa9 region4 (1316 rows affected)
Tak więc czeka nas usunięcie ponad 8.684 wierszy
Możemy jeszcze sobie sprawdzić ile jest wierszy dla każdego randomID
SELECT randomID, COUNT(*) as count
FROM [Database1].[dbo].[duplikaty]
GROUP BY randomID
order by randomID;
randomID count ----------- ----------- 0 488 1 993 2 1024 3 949 4 982 5 1028 6 984 7 1055 8 1004 9 1030 10 463 (11 rows affected)
Poniższe zapytanie przypisuje każdemu wierszowi kolejny numer. W przypadku zdublowanych wierszy będą to kolejne liczby np
10 nazwa9 region9 1 10 nazwa9 region9 2 10 nazwa9 region9 3
SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty];
randomID nazwa region DuplicateCount ----------- ---------- ---------- -------------------- 0 nazwa0 region0 1 0 nazwa0 region1 1 0 nazwa0 region1 2 0 nazwa0 region10 1 0 nazwa0 region10 2 0 nazwa0 region2 1 ... 10 nazwa9 region8 3 10 nazwa9 region8 4 10 nazwa9 region8 5 10 nazwa9 region9 1 10 nazwa9 region9 2 10 nazwa9 region9 3 (10000 rows affected)
naszym zadaniem będzie teraz usunięcie wszystkich wierszy z DuplicateCount większym niż 1. Przed uruchomieniem DELETE najpierw sprawdzamy co nam wykona polecenie (dobra praktyka!)
WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
SELECT * FROM CTE WHERE DuplicateCount > 1;
randomID nazwa region duplicatecount ----------- ---------- ---------- -------------------- 0 nazwa0 region1 2 0 nazwa0 region10 2 0 nazwa0 region2 2 0 nazwa0 region2 3 0 nazwa0 region3 2 ... 10 nazwa9 region8 4 10 nazwa9 region8 5 10 nazwa9 region9 2 10 nazwa9 region9 3 (8684 rows affected)
Select wzrócił nam 8683 wiersze do usunięcia. Zamieniamy SELECT na DELETE i uruchamiamy polecenia
WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
DELETE FROM CTE WHERE DuplicateCount > 1;
(8684 rows affected)
Sprawdzamy ile wierszy zostało w tabeli:
select COUNT(*) as [REKORDY] from [Database1].[dbo].[duplikaty];
REKORDY ----------- 1316 (1 row affected)
Sprawdzamy czy nie ma dwóch takich samych wierszy (powinno zwrócić tyle samo co powyższe zapytanie):
select DISTINCT * from [Database1].[dbo].[duplikaty];
randomID nazwa region ----------- ---------- ---------- 0 nazwa0 region0 0 nazwa0 region1 0 nazwa0 region10 0 nazwa0 region2 0 nazwa0 region3 ... 10 nazwa9 region6 10 nazwa9 region7 10 nazwa9 region8 10 nazwa9 region9 (1316 rows affected)
Czyszczenie tabeli zostało zakończone poprawnie. Oczywiście można to samo zrobić na kilka innych sposobów m.in. wykorzystując wspomniane wcześniej narzędzia Integration Services (SSIS).
Nie będę tu już opisywał jak stworzyć za pomocą Visual Studio taki projekt. Możecie to znaleźć na wcześniejszych wpisach na mojej stronie.
Do naszego projektu wrzucamy DataFlowTask.
W środku tworzymy OLEDBSource
Konfigurujemy połączenie do naszej bazy i tabeli ze zdublowanymi wierszami
W projekcie do OLEDBSource (który powinien już nie "świecić" się na czerwono) podłączamy element SORT (on się jeszcze świeci na czerwono bo go jeszcze nie skonfigurowaliśmy).
W konfiguracji SORT'a na dole ekranu zaznaczamy opcję "Remove rows with duplicate sort values"
Podłączamy element Delivered Column
A na ich połączeniu wstawiamy "Data Viewer" który pozwoli nam na podglądnięcie przesyłanych danych.
Uruchamiamy nasz projekt
Pojawia się tabelka z 1316 unikalnymi wierszami naszej tabeli
Taką samą ilość wierszy widzimy na schemacie przepływu w naszym projekcie.
