Hello Everyone,
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
Thanks for your help
Kapil
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems
OPEN childcur_inventory
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
WHILE @@FETCH_STATUS = 0
BEGIN ---@@FETCH_STATUS = 0
IF @Neighbour_State = 'CN' OR @Neighbour_State = 'MEX'
Update #InventoryItems SET Group_Id = @rec_cnt
ELSE
SELECT @v_cnt = COUNT(*) FROM [nti].[InventoryItems] i
where Year = (Select year( getdate() )) AND
i.StateCode_I3 = CAST(@Neighbour_State as smallint) AND
i.StateCode_I3 <> @State AND
i.TunnelNo_I1 = @Border_Bridge_Structure_Number
if @v_cnt > 0
BEGIN
Update #InventoryItems SET Group_Id = @rec_cnt, Valid_Item99 = 'Y',Group_Id = @rec_cnt
Select @other_st_name = StateCode from [nti].[States] where statecode = @Neighbour_State
INSERT INTO #InventoryItems(Structure_Number,State,Highway_District,Country_Code,Place_Code,Item_7_Facility_Carried,Owner,Neighbor_State,Percent_Responsiblity,Border_Bridge_Structure_Number, Inspection_Responsibility,Valid_Item99,Group_Id,Str_id)
Select
[TunnelNo_I1] ,
[StateCode_I3] ,
[HighwayDistrict_I6] ,
[CountyCode_I4] ,
[PlaceCode_I5] ,
[FacilityCarried_I10] ,
[Owner_C1] ,
[BorderState_I15] ,
[BorderFinResp_I16] ,
[BorderTunnelNo_I17] ,
[BorderInspectResp_I18] ,
null as VALID_ITEM99,
@other_st_name + @rec_cnt,
'2'
FROM [nti].[InventoryItems]
WHERE Year = (Select year( getdate() )) AND
StateCode_I3 = CAST(@Neighbour_State as smallint) AND
TunnelNo_I1 = @Border_Bridge_Structure_Number
END
ELSE
BEGIN
Update #InventoryItems SET Group_Id = @rec_cnt,Valid_Item99 = 'N'
PRINT 'HELLO WORLD'
INSERT INTO #InventoryItems(Structure_Number,State,Highway_District,Country_Code,Place_Code,Item_7_Facility_Carried,Owner,Neighbor_State,Percent_Responsiblity,Border_Bridge_Structure_Number, Inspection_Responsibility,Valid_Item99,Group_Id,Str_id)
SELECT Top 1 null, State,null as Highway_District, null as Country_Code, null as Place_Code, null as Item_7_Facility_Carried , null as Owner, null as Neighbor_State ,null as Percent_Responsiblity, null as Border_Bridge_Structure_Number , null as Inspection_Responsibility , null as Valid_Item99 , @rec_cnt ,'3'
END
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
END --- End of While @@FETCH_STATUS = 0
CLOSE childcur_inventory
DEALLOCATE childcur_inventory
SELECT * FROM #InventoryItems
DROP TABLE #InventoryItems
END