June 2, 2008 at 4:00 am
I am having 200 records starts with 1 and endswith 200
Some of the records are deleted in the series. (23,56,78)
How to identify the deleted records id only in a table
Thanks
June 2, 2008 at 5:05 am
use "not in" clause for the identity column values...
in where clause..
Cheers!
Sandy.
--
June 2, 2008 at 5:27 am
There are several options on how to do this. You might want to check out this article: http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/
or this one:
http://vadivel.blogspot.com/2007/04/find-missing-numbers-gaps-within-table.html
[font="Verdana"]Markus Bohse[/font]
June 2, 2008 at 6:01 am
The best way to do this is to generate a list of numbers that "should" be there and then use a left joint to find the missing ones.
This can be done through a temp table and a cursor, a CTE, or (often most efficiently) with a tally table.
June 2, 2008 at 6:14 am
try some form of this -
select a.id - 1 from table_name a where not exists( select b.id from table_name b where a.id = b.id + 1)
the first record will show the the initial value of the identity - 1; you need to ignore this.
June 2, 2008 at 6:21 am
This method can be used to show you the beginning of ends of gaps, but if you are missing three values in a row, getting the one in the middle is a problem.
It can be done through a recursive CTE with this method, but it ends up being more efficient to use a recursive CTE to make an entire list of possible values and using a left join. At that point, a tally table is going to be faster.
It will depend on if this is going to be a regularly run process or not.
June 2, 2008 at 6:26 am
Rajan John (6/2/2008)
try some form of this -select a.id - 1 from table_name a where not exists( select b.id from table_name b where a.id = b.id + 1)
the first record will show the the initial value of the identity - 1; you need to ignore this.
That's OK as long as you don't have two or more consecutive gaps. Try running your code against this table:
use tempdb
create table table_name (id int)
insert into table_name
select number from master..spt_values
where name is NULL
and number not between 20 and 30
John
June 2, 2008 at 6:26 am
yes, I agree. I thought the missing values wont be continuous. And yes, recursive CTE is an answer.
June 2, 2008 at 6:33 am
Hi John,
I tried your query. It's giving the correct output. The output is starting with 0 only, If the id is starting with 1. If it is starting with 0, in the condition give like (a.id - 1) != -1.
If u give like the following it will give the expected result, I think.
select a.id - 1 from table_name a where not exists( select b.id from table_name b where a.id = b.id + 1) and (a.id - 1) != 0
Just add one more condition. That's all.
Regards,
Preetha SG
June 2, 2008 at 6:38 am
preetha.sathyamoorthy (6/2/2008)
Hi John,I tried your query. It's giving the correct output. The output is starting with 0 only, If the id is starting with 1. If it is starting with 0, in the condition give like (a.id - 1) != -1.
If u give like the following it will give the expected result, I think.
select a.id - 1 from table_name a where not exists( select b.id from table_name b where a.id = b.id + 1) and (a.id - 1) != 0
Just add one more condition. That's all.
Regards,
Preetha SG
No, it's still not right. The identity range in my table is missing 11 numbers (from 20 to 30) and yet the query only returns the number 30.
John
June 2, 2008 at 7:04 am
Hi,
Try this this will give you the missing ID's.I am Creating and Populating a Tally table and comparing the IDs with it.
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Declare @Temp table (a int )
insert into @Temp
select 4 union
select 5 union
select 8 union
select 9 union
select 11 union
select 12 union
select 14 union
select 16
select * from @Temp
Declare @Start int
Declare @End int
set @Start = (select min(a) from @Temp)
set @End = (select max(a) from @temp)
select * from tally
left join @Temp t
on Tally.n=t.a
where n between @Start and @End and a is null
Rajesh
June 2, 2008 at 7:10 am
Thanks for the query.
In my table two rows are missing, first row it shows 0 and next as 51.
51 is corect output before that 49 is missing.
It wont show 49..
June 2, 2008 at 7:14 am
HI,
Unless I am missing the problem.
A simple left join to a tally table with an is null clause in the where should always return the missing numbers in your table.
Unless the tally table is not big enough.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 2, 2008 at 10:29 pm
Hi Sharma,
I Have assumed your Ids starts from 1,so N in the Tally Table starts from 1 ,if you want to start from 0 ,make the Identity(1,1) in tally table as Identity(0,1) and Proceed.
It wont miss the id 49,but still if you find difficulties post the code and sample Datas along with your Table Schemas to help you further.
Regards,
Rajesh
June 3, 2008 at 7:29 am
Take a look at the following code that can demonstrate a technique that uses an auxiliary table of numbers:
-- START WITH A TABLE OF THE NUMBERS FROM 1 to 200
SET NOCOUNT ON
DECLARE @NUMS TABLE (n int NOT NULL PRIMARY KEY)
DECLARE @max-2 AS int, @rc AS int
SET @max-2 = 200
SET @rc = 1
INSERT INTO @NUMS VALUES(1)
BEGIN
INSERT INTO @NUMS SELECT n + @rc FROM @NUMS
END
INSERT INTO @NUMS
SELECT n + @rc
FROM @NUMS
-- NOW DUPLICATE THE TABLE AND REMOVE A COUPLE RECORDS
DECLARE @EXISTING TABLE (n int NOT NULL PRIMARY KEY)
INSERT INTO @EXISTING
SELECT n
FROM @NUMS
WHERE n NOT IN (23,56,78)
-- NOW COMPARE THE TWO AND SHOW ONLY THOSE THAT GOT REMOVED
SELECT n
FROM @NUMS
WHERE n NOT IN (SELECT n FROM @EXISTING)
Does that help?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply