Check missing number before insert using while loop in SQL Server

PRA
 
on Feb 24, 2021 02:55 AM
531 Views

Hi!

I used below script its working. I have missing Id it was incorrect I deleted on.

Now I want program must check missing Id if exist insert missing Id for new person else continues. 

CREATE TABLE [dbo].[Person](
	[Id] [int] NOT NULL,
	[Name] [nchar](10) NOT NULL,
	[RegDate] [date] NOT NULL
)
declare @Id int,@i int,@CurDate DATETIME
    SET  @CurDate = GETDATE()
    SELECT @i = ISNULL(MAX(Id),0) FROM person where YEAR([RegDate]) = YEAR(@CurDate)
    IF DAY(@CurDate) = 1 AND MONTH(@CurDate) = 1 AND (SELECT COUNT(Id) FROM person WHERE YEAR(RegDate) = YEAR(@CurDate)) = 0
    BEGIN
    SET @i = 1
    END
    if isnull(@i,0) = 0
    begin
    set @Id = 1
    select @Id
    end 
    else
    set @Id = @i + 1

insert into person(Id, Name, RegDate) values (199, 'Rustam', '2020-12-28')
insert into person(Id, Name, RegDate) values (200, 'Asror', '2020-12-29')
insert into person(Id, Name, RegDate) values (201, 'Muhammad', '2020-12-29')
insert into person(Id, Name, RegDate) values (202, 'Sindbod', '2020-12-31')
insert into person(Id, Name, RegDate) values (2, 'Rustam', '2021-01-02')
insert into person(Id, Name, RegDate) values (3, 'Firuz', '2021-02-21')
insert into person(Id, Name, RegDate) values (5, 'Sadriddin', '2021-02-22')
insert into person(Id, Name, RegDate) values (9, 'Safar', '2021-02-23')
insert into person(Id, Name, RegDate) values (10, 'Samar', '2021-02-24')
insert into person(Id, Name, RegDate) values (@Id, 'Said', '2021-02-24')

select * from person

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Feb 24, 2021 02:58 AM

Hi PRA,

Refer below link.

Select missing number from a column using while loop in SQL Server

Create a function and call to insert in database.