May 12, 2015 at 6:47 am
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
May 12, 2015 at 6:52 am
Please post a script that anyone trying to help you could run on their machine.
We need table scripts, sample data and expected output.
See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for posting guidelines.
-- Gianluca Sartori
May 12, 2015 at 7:19 am
FETCH NEXT
Discard this, yes, the script has FETCH in a loop.
May 12, 2015 at 7:25 am
Let's start with formatting this so we can see what is going on.
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
I don't see any reason to use a cursor here. Also, do you realize that you have several update statements in here with no where clause? That means it will update the entire table. Your code will never end because you only have the fetch keyword. You should have FETCH NEXT (well really you should get rid of this cursor). Then your top 1 query has a flaw too, it has no where order by clause. That means you don't care which row it returns.
To be totally honest, the best fix for this is a complete rewrite. It can almost certainly be done in a set based approach with only 1 or 2 queries instead of looping through the whole resultset over and over.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 12, 2015 at 8:02 am
Then your top 1 query has a flaw too, it has no where clause.
No ORDER BY clause, I think you may have meant.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 12, 2015 at 8:08 am
Phil Parkin (5/12/2015)
Then your top 1 query has a flaw too, it has no where clause.
No ORDER BY clause, I think you may have meant.
Err...yeah. :blush: Thanks Phil.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply