Szukanie brakujacych rekordow w tabeli sql server 2014

Mam takim problem . Jak znaleŹĆ wszyskie brakujace rekordy w tabeli , ale za pomoca cursora.
Zrobilem tak, ale nie znaduje mi 4 dziury:

CREATE TABLE #TestTable (id INT
);

INSERT INTO #TestTable (ID) VALUES (1)
INSERT INTO #TestTable (ID) VALUES (2)
INSERT INTO #TestTable (ID) VALUES (5)
INSERT INTO #TestTable (ID) VALUES (6)
INSERT INTO #TestTable (ID) VALUES (8)
INSERT INTO #TestTable (ID) VALUES (9)
INSERT INTO #TestTable (ID) VALUES (10)
INSERT INTO #TestTable (ID) VALUES (12)

DECLARE Cursor1 CURSOR FOR

SELECT id FROM #TestTable

DECLARE @T INT, @id int

SET @T = 0
OPEN Cursor1;
FETCH NEXT FROM Cursor1 into @id;
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @T = @T+1
   if @id <> @T

  print CAST(@t AS VARCHAR) +  '  jest brakujaca' + '"  "'  + CAST(@id AS VARCHAR) + '  =id'
  
   SET @T = @id
  
 FETCH NEXT FROM Cursor1  into @id;	  
 
 END;

CLOSE Cursor1;
-
DEALLOCATE Cursor1;

--select id from #TestTable
DROP TABLE #TestTable

Bo ty przechodzisz wszystkie rekordy w tabeli, i jeżeli id <> jest różne od założonego id to wypisujesz pierwszy, zupełnie ignorując fakt, że luka może mieć więcej niż jedno id. Dodatkowo w kursorze bierzesz nieposortowane dane, co zupełnie nie współgra z metodą którą obrałeś do sprawdzenie braków:

CREATE TABLE #TestTable (id INT
);

INSERT INTO #TestTable (ID) VALUES (1)
INSERT INTO #TestTable (ID) VALUES (2)
INSERT INTO #TestTable (ID) VALUES (5)
INSERT INTO #TestTable (ID) VALUES (6)
INSERT INTO #TestTable (ID) VALUES (8)
INSERT INTO #TestTable (ID) VALUES (9)
INSERT INTO #TestTable (ID) VALUES (10)
INSERT INTO #TestTable (ID) VALUES (12)

DECLARE Cursor1 CURSOR FOR

--tu brakowało posortowania po id
SELECT id FROM #TestTable order by id

DECLARE @T INT, @id int

SET @T = 0
OPEN Cursor1;
FETCH NEXT FROM Cursor1 into @id;
WHILE @@FETCH_STATUS = 0
BEGIN

	SET @T = @T+1
	if @id <> @T
	-- tu musisz wypisać wszystkie braki pomiędzy poprzednim i bieżącym id
	WHILE @T < @id
	begin
		print CAST(@T AS VARCHAR) + ' jest brakujaca' + '" "' + CAST(@id AS VARCHAR) + ' =id'
		set @T = @T+1
	end
	SET @T = @id

	FETCH NEXT FROM Cursor1 into @id;

END;

CLOSE Cursor1;
DEALLOCATE Cursor1;

--select id from #TestTable
DROP TABLE #TestTable

Czum to musi być w kursorem?

Panczo napisał(a):

Bo ty przechodzisz wszystkie rekordy w tabeli, i jeżeli id <> jest różne od założonego id to wypisujesz pierwszy, zupełnie ignorując fakt, że luka może mieć więcej niż jedno id. Dodatkowo w kursorze bierzesz nieposortowane dane, co zupełnie nie współgra z metodą którą obrałeś do sprawdzenie braków:

CREATE TABLE #TestTable (id INT
);

INSERT INTO #TestTable (ID) VALUES (1)
INSERT INTO #TestTable (ID) VALUES (2)
INSERT INTO #TestTable (ID) VALUES (5)
INSERT INTO #TestTable (ID) VALUES (6)
INSERT INTO #TestTable (ID) VALUES (8)
INSERT INTO #TestTable (ID) VALUES (9)
INSERT INTO #TestTable (ID) VALUES (10)
INSERT INTO #TestTable (ID) VALUES (12)

DECLARE Cursor1 CURSOR FOR

--tu brakowało posortowania po id
SELECT id FROM #TestTable order by id

DECLARE @T INT, @id int

SET @T = 0
OPEN Cursor1;
FETCH NEXT FROM Cursor1 into @id;
WHILE @@FETCH_STATUS = 0
BEGIN

	SET @T = @T+1
	if @id <> @T
	-- tu musisz wypisać wszystkie braki pomiędzy poprzednim i bieżącym id
	WHILE @T < @id
	begin
		print CAST(@T AS VARCHAR) + ' jest brakujaca' + '" "' + CAST(@id AS VARCHAR) + ' =id'
		set @T = @T+1
	end
	SET @T = @id

	FETCH NEXT FROM Cursor1 into @id;

END;

CLOSE Cursor1;
DEALLOCATE Cursor1;

--select id from #TestTable
DROP TABLE #TestTable

Czum to musi być w kursorem?

Bo to moje iucze sie kursowow na stare lata.. Inne sposoby moje i cudze mam.

Czum to musi być w kursorem?

Bo to moje iucze sie kursowow na stare lata.. Inne sposoby moje i cudze mam.

Mam

DECLARE Cursor1 CURSOR FOR

SELECT id FROM TblTest order by  id asc

DECLARE @T INT, @id int

SET @T = 1 
OPEN Cursor1;
FETCH NEXT FROM Cursor1 into @id;
WHILE @@FETCH_STATUS = 0
BEGIN

--SET @T = @T+1
 
 if   @id = @t
 
 --print CAST(@t AS VARCHAR) +  '  jest brakujaca' + '"  "'  + CAST(@id AS VARCHAR) + '  =id'

 --SET @T = @id
 
 FETCH NEXT FROM Cursor1  into @id;	
 else
  print CAST(@t AS VARCHAR) +  '  jest brakujaca' 
 SET @T = @T+1

 END;

CLOSE Cursor1;
DEALLOCATE Cursor1;

SQL to praca na zbiorach, użyj więc stosownych operatorów. Jest taki trick, gdzie generujesz zbiór wszystkich możliwych wartości i odejmujesz od niego to co aktualnie jest w tabeli. Otrzymujesz różnicę, czyli te brakujące rekordy.
Tu w zależności od bazy trzeba wymyślić sposób na wygenerowanie tego pierwszego zbioru. W Oracle robiło się to za pomocą klauzuli CONNECT BY, w MS SQL pewnie uda się z klauzulą WITH.

Coś w rodzaju:

WITH wszystko as
(
select kolejna_liczba
from magiczne zapytanie
co zwróci wartości od 1 do MAX(ID) z TestTable 
)
select kolejna_liczba
from wszystko
minus
select ID
from TestTable;

--Drop TABLE #tabTest
--Drop TABLE #tabTest1


CREATE TABLE #tabTest (lp int,id INT, nr int, ileRazydodacpo1Donr  int) 
CREATE TABLE #tabTest1 ( Nr_Brakujacy int )   
   ;with cte
as
(
select  ROW_NUMBER() OVER( ORDER BY (SELECT NULL))as lp,id,LAG(id,1,0) over(ORDER BY id) as nr,id-LAG(id,1,0) over(ORDER BY id)as r
from tblTest  
)--select ROW_NUMBER() OVER( ORDER BY (SELECT NULL))as lp,id,nr, r-1 as iledodac from cte where r>1 
INSERT INTO #tabTest 
select ROW_NUMBER() OVER( ORDER BY (SELECT NULL))as lp ,id,nr, r-1 as ileRazydodacpo1Donr     from cte  where r>1
--Select max(ileRazydodacpo1Donr) from #tabTest
--Select * from #tabTest

DECLARE @l INT,
@nr int,
@ileRazydodacpo1Donr int,
@l1 int, --licznik drugiej petli
@Ile  int,
@Ile1  int

set @ile =(Select max(ileRazydodacpo1Donr) from #tabTest)
set @ile1 =(Select COUNT(ileRazydodacpo1Donr) from #tabTest)
set @nr=0 
set @l1=0
SET @l=0


WHILE @l <= @ile --(Select max(ileRazydodacpo1Donr) from #tabTest= daje blad w wyniku mienna ok)
begin
SET @l  = @l  + 1  
  
 While @l1 <= @ile1   -- 4
 begin

 set @l1 = @l1+1

 set  @ileRazydodacpo1Donr = (Select ileRazydodacpo1Donr
 FROM #tabTest WHERE lp = @l1 )
 
 set  @Nr = (Select Nr
 FROM #tabTest WHERE lp = @l1 )

IF  @ileRazydodacpo1Donr >0
  
BEGIN
 
  set  @ileRazydodacpo1Donr = (Select ileRazydodacpo1Donr-1
     FROM #tabTest WHERE lp = @l1 )
  set  @Nr = (Select Nr+1
     FROM #tabTest WHERE lp = @l1 )

 
 print @Nr
 INSERT INTO #tabTest1(Nr_Brakujacy)Values(@nr)
 UPDATE  #tabTest set Nr = @Nr,ileRazydodacpo1Donr = @ileRazydodacpo1Donr WHERE lp = @l1
 --UPDATE  #tabTest set Nr = @Nr WHERE lp = @l

 END


End 

--wyzerowuje sie 
set @l1 =0 

--END
END


--Select * from #tabTest
Select  ROW_NUMBER() OVER( ORDER BY (SELECT NULL))as Lp,Nr_Brakujacy from #tabTest1 order by Nr_Brakujacy asc
Drop TABLE #tabTest
Drop TABLE #tabTest1

Skorzystałem trochę z podpowiedzi i jest super szybkie wyszukiwanie. W sieci nie znalazłem szybszej procedury..
poz. Jerzy.

Używaj znaczników do kodu, bo tak to jest nieczytelne.
Poszukaj rozwiązania bez pętli to będzie jeszcze szybciej...

Konczac dyskusje o dziurach w tabeli musze powiedziec , ze nie znalazlem w sieci rozwiazania dobrego. Selekty pokazuja braki numerow , ale nie wszystkie. W moim przyadku dotyczylo to 16 dziur pojedynczych i zblokowanych.Tylko jeden select na SQLPedi Jakuba Kasprzaka daje ten sam wynik co moje procedury z tym, ze moj select z petla jet szybszy. Obala to chyba teorie o wyzszosci selecta nad innymi procedurami.
Poz. Jerzy

Na jakiej ilości danych to testowaleś?

Obala to chyba teorie o wyzszosci selecta nad innymi procedurami.

W kontekście tsql, to stwierdzenie nie ma sensu. Procedura może mieć jednego selecta, albo nie mieć go wcale...

100001.Dzury 3,4,11,801,802,5000,9003,9004,9005,99000,99001,99002,99003,99004,99999,100000

no taka ilość rekordów nie pozwala na dawanie stwierdzeń, że pętle są szybsze.

Jeżeli zwiększymy dane 10 krotnie:

--dane testowe

CREATE TABLE [dbo].[tblTest](
	[id] [int] NOT NULL
) ON [PRIMARY]
GO
truncate table [tblTest]
go
with cte as (
SELECT n1.n + 10*n2.n + 100*n3.n + 1000*n4.n + 10000*n5.n + 100000*n6.n lp
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n2(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n3(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n4(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n5(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n6(n)
)

insert into tblTest
select * from cte where  lp > 0


delete from tblTest where id in (3,4,11,801,802,5000,9003,9004,9005,99000,99001,99002,99003,99004,99999,100000,
30,400,110,8013,8302,52000,119003,9004,9005,99000,99001,199002,99003,99004,299999,3100000)

i puścimy twoje rozwiązanie:

To statystyki wykonania wygladają tak:

Jeżeli wykonamy to za pomocą selecta:

with cte as (
SELECT 
	n1.n + 10*n2.n + 100*n3.n + 1000*n4.n + 10000*n5.n + 100000*n6.n lp
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n2(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n3(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n4(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n5(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n6(n)
)



select lp id from cte lp
left join tbltest t on lp.lp=t.id
where t.id is null
and lp > 0

I tak najwięcej tracimy na generowaniu danych, ja osobiście, korzystam z raz wygenerowanej tabeli z kolejnymi numerami, bo taka czasami się przydaje i nie trzeba zapytań do generowania liczb i wtedy schodzimy jeszcze niżej z czasami:

select lp id from nr lp
left join tbltest t on lp.lp=t.id
where t.id is null
and lp > 0

a możesz porównać tego left joina z operacją MINUS? select lp.id from nr lp minus select t.id from tbltest t

@Robertos w tsql odpowiednikiem jest EXCEPT

Wyniki:

with cte as (
SELECT 
	n1.n + 10*n2.n + 100*n3.n + 1000*n4.n + 10000*n5.n + 100000*n6.n lp
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n2(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n3(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n4(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n5(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n6(n)
)

 select lp from cte  EXCEPT select t.id from tbltest t

 select lp from nr  EXCEPT select t.id from tbltest t

Ciekawe wyniki, wolniejszy jest z tabela szybszy z dynamicznymi danymi. Oczywiście to wyniki trzeba traktować raczej jako dowód anegdotyczny, bo i środowisko specjalnie nieprzygotowane, ani
tabele miarodajne itd.