w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Fill in missing rows in a table SQL

Try this seems works fine

create table #temp(iCase int, idcode int,value int)
Insert into #temp values(2,3,122)
Insert into #temp values(2,6,52)
Insert into #temp values(2,15,121)
Insert into #temp values(3,1,11)
Insert into #temp values(3,3,555)
Insert into #temp values(3,6,322)


create table #Val(Id int)

declare @count int =1

while (@count<=18)
begin
    insert into #Val values(@count)
    set @count=@count+1
end

DECLARE @CaseId INT
DECLARE @DataCursor CURSOR
SET @DataCursor = CURSOR FOR
SELECT distinct iCase
From #temp
OPEN @DataCursor
FETCH NEXT
FROM @DataCursor INTO @CaseId
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #temp
    SELECT @CaseId,Id,null
    FROM #Val
    WHERE Id NOT IN (
    SELECT idcode
    FROM #temp
    WHERE iCase=@CaseId )

FETCH NEXT
FROM @DataCursor INTO @CaseId
END
CLOSE @DataCursor
DEALLOCATE @DataCursor


Select * from #temp




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.