question

Hi, I have a table with a numerical column where I have gap between entries. I should insert new entries in the table and fill this column with newer number but corresponding to the gap I have. It not working and I suppose its comminmg from some kind of lock on the table. In the insert I make a select from the same table. Someone have a idea how can I do it? There is my example: DECLARE @NewDN integer = 0 DECLARE @tOutput TABLE (OutputId Integer IDENTITY(1,1) PRIMARY KEY, DN nvarchar(7), NAME nvarchar(20)) DECLARE @tListNo TABLE (DN nvarchar(7) PRIMARY KEY) DECLARE @tUsers TABLE (UserId Integer IDENTITY(1,1) PRIMARY KEY, [UserName] nvarchar(20)) -- Fill a table with a list of possible number WHILE (@NewDN < 1000) BEGIN SELECT @NewDN = @NewDN + 1 INSERT INTO @tListNo (DN) VALUES ('450' + RIGHT('00000' + CONVERT(VARCHAR,@NewDN),3)) END -- Put some dummy records in the output table INSERT INTO @tOutput (DN,NAME) VALUES ('450002','Pierre') INSERT INTO @tOutput (DN,NAME) VALUES ('450004','Jean') INSERT INTO @tOutput (DN,NAME) VALUES ('450006','Jacques') INSERT INTO @tOutput (DN,NAME) VALUES ('450008','Fred') -- Put some dummy records in the Users table INSERT INTO @tUsers ([UserName]) VALUES ('Annie') INSERT INTO @tUsers ([UserName]) VALUES ('Lise') INSERT INTO @tUsers ([UserName]) VALUES ('Sophie') INSERT INTO @tUsers ([UserName]) VALUES ('Francoise') -- Get a the first Free number (For Debug) SELECT TOP 1 t1.DN FROM @tListNo t1 LEFT JOIN @tOutput t2 ON t1.DN=t2.DN WHERE t2.DN IS NULL; -- Insert records in the table INSERT INTO @tOutput (DN, NAME) SELECT (SELECT TOP 1 t1.DN FROM @tListNo t1 LEFT JOIN @tOutput t2 ON t1.DN=t2.DN WHERE t2.DN IS NULL ) as DN,[UserName] FROM @tUsers -- Should get 450009 SELECT TOP 1 t1.DN FROM @tListNo t1 LEFT JOIN @tOutput t2 ON t1.DN=t2.DN WHERE t2.DN IS NULL; -- Debug SELECT * FROM @tListNo SELECT * FROM @tOutput Thanks in advance Jacques