set @totalHALBS = (select count (distinct lvl2) as Total  from  vwSAP_BOMAnalysisByFG vwbe inner join SAPItemMaster sim on vwbe.Lvl2 = sim.MATNR WHERE FG = '9120B' and sim.MTART = 'HALB')

SET @finishHALBS = 0

WHILE (@finishHALBS <=  @totalHALBS)
BEGIN
SET @finishHALBS = @finishHALBS + 1
INSERT INTO #BOMAnalysis
select distinct vwbe.Lvl2, sim.MAKTG,@finishHALBS,1,sim.MTART,'2'  from  vwSAP_BOMAnalysisByFG vwbe inner join SAPItemMaster sim on vwbe.Lvl2 = sim.MATNR WHERE FG = '9120B' and sim.MTART = 'HALB'
end

select * from #BOMAnalysis order by mattype