Insert Single Record in Temp Table

Transact-SQL
1

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

2

Declare a flag outside the main loop:

declare @flag bit; set @flag = 0

inside the loop, where you want to insert the row:

if @flag = 0 begin
    insert into #temptable
   select null,null, ...
   set @flag = 1
end
1 Like
3

Thank you

It worked

Kapil