February 8, 2006 at 1:58 pm
In My database ,a column Message_reference VARCHAR2(10) is having sequncial values.
But some of the entries are missing . i want to insert those entries in a table1.
e.g. message_reference
1
2
4
7
then i want missing number in table1
3,5,6
Regards
Amol.
February 8, 2006 at 2:25 pm
I'm sure there is probably a more efficient way to do this, but this would do the trick.
declare @counter int
declare @max-2 int
select @max-2 = max(mesage_reference) from table_name(original table)
select @counter = 1
while @counter < @max-2
begin
if not exists(select 1 from table_name where mesage_reference = @counter)
insert into table1(column_name) select @counter
set @counter = @counter + 1
end
February 8, 2006 at 2:47 pm
For a set-based solution, you need a table of all possible numbers. You can get this by cross-joining 2 tables of known large size:
-- Limit to as many rows as you need. Use master.dbo.syscomments
-- to generate large number of rows
Select Top 100000 Identity(int, 1, 1) As SequenceNumber
Into #AllNumbers
From master.dbo.syscomments as c1
Cross Join master.dbo.syscomments As c2
Insert Into Table1 (MissingNumber)
Select a.SequenceNumber
From #AllNumbers As a
Where Not Exists (
Select *
From YourTable As t
Where t.Message_reference = a.SequenceNumber
)
February 9, 2006 at 4:13 am
I guess the above is faster if you add an index on #AllNumbers:
ALTER TABLE #AllNumbers
ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(SequenceNumber)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply