Need to delete a table before going into cursor

Question

Post reply

Need to delete a table before going into cursor

Cory Slaney

SSCrazy

Points: 2459
More actions
May 30, 2005 at 6:09 am

#66126

Please have a look at the code below:
CREATE procedure rt_edt_sp
as
SET ANSI_NULLS ON
SET NOCOUNT OFF
declare @trg_sub_code varchar(8)
declare @mid varchar(10)
declare @callsign varchar(6)
declare @src varchar(1)
declare @id varchar(4)
declare @cid varchar(6)
declare @utc varchar(10)
declare @tlat varchar(8)
declare @tlng varchar(9)
declare @TCC varchar(3)
declare @zone varchar(3)
declare @io varchar(1)
declare @distio varchar(6)
declare @va varchar(2)
declare @nat varchar(3)
declare @fish varchar(3)
declare @crs varchar(5)
declare @spd varchar(5)
declare @vid varchar(6)
declare @name varchar(32)
declare @side varchar(12)
declare @call varchar(10)
declare @comm varchar(100)
declare @row varchar(400)
declare e_cursor cursor for select data
                            from rt_edtstaging

open e_cursor
fetch next from e_cursor into @row
set @mid=substring(@row,1,10)
print @mid
delete from rt_edt
where mid=@mid
--insert into rt_mission table
--if (select mseq from rt_mission where mid=@mid) is null
--begin
--end
WHILE(@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
  BEGIN

  IF len(@row) <= 16
   BEGIN
   set @mid=substring(@row,1,10)
   set @callsign=substring(@row,11,6)
   --insert into rt_edt(mid,callsign)
   --values (@mid,@callsign)
   END
  ELSE
   begin
   set @src=substring(@row,1,1)
   set @id=substring(@row,2,4)
   set @cid=substring(@row,6,5)
   set @utc=substring(@row,11,10)
   set @tlat=substring(@row,38,8)
   set @tlng=substring(@row,46,9)
   set @TCC=substring(@row,75,3)
   set @zone=substring(@row,78,3)
   set @io=substring(@row,81,1)
   set @distio=substring(@row,82,6)
   set @va=substring(@row,88,2)
   set @nat=substring(@row,90,3)
   set @fish=substring(@row,97,3)
   set @crs=substring(@row,101,5)
   set @spd=substring(@row,106,5)
   set @vid=substring(@row,111,6)
   set @name=substring(@row,117,32)
   set @side=substring(@row,149,12)
   set @call=substring(@row,161,10)
   set @comm=substring(@row,171,100)

  select @trg_sub_code=f.subcode
  from fn_sub_desc(@src,@nat,@tcc) f
  insert into rt_edt
  values (@mid,@callsign,@src,@id,@cid,@utc,@tlat,@tlng,@tcc,@zone,@io,@distio,@va,@nat,
   @fish,@crs,@spd,@vid,@name,@side,@call,@comm,@trg_sub_code)
  END

  delete from rt_edt
  where mid=@mid and (src is null or src='')
  fetch next from e_cursor into @row
END
end
close e_cursor
deallocate e_cursor
GO
The delete statement bolded above does not seem to be working. Does anyone have any suggestions for deleting the rt_nav table before inserting all the new records? This is the way we refresh 'real-time' data from our application each time we receive new data.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

If you want to delete all the records from the table you can do something like this :

Delete from rt_edt

if you don't want the delete to be fully logged then you can use this :

Truncate table rt_edt

What exactly are you trying to accomplish?

Cory Slaney SSCrazy Points: 2459 More actions · Answer 2

Thanks for the suggestions Remi. But it should only be deleting records based on the current value of 'mid'.

The rt_edt table is used to store data for real-time updates for our application. This table should be cleared out first before the next update is inserted into the table. If not, we get duplicates of our data which really slow down the application. The unique identifier is the 'mid' value which is parsed out of a text file and then inserted into the rt_edtstaging table. The 'WHILE' part of the stored procedure (as above), takes each row in the rt_edtstaging, parses out the necessary data using substring and then inserts it into rt_edt which is the refresh data for the real-time application. Everything seems to work fine except the delete statement. I cannot figure out why! Any further suggestions? Here is a sample of the data for the substring to get the actual 'mid' value.

BKNL05-271C-GMRS

The delete then depends on this value. Maybe it is not being parsed out properly?

Any suggestions would be greatly appreciated.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Might sound simplistic... but is it possible that you don't set @mid in the else part of the proc?

Cory Slaney SSCrazy Points: 2459 More actions · Answer 4

The data being parsed in from the rt_edt staging file looks like this:

BKNL05-271C-GMRS

U0001V00011117101887+47.6203-052.7398+47.5692-052.6990 3.48151.67 0.0VU-3L-I195.7909UNK----000N161.3 0.2000000MAERSK CHIGNECTO 031600600 VCJC AIS TARGET

N0002A00011117101224+47.6212-052.7370+47.6212-052.7367 0.01 98.73 0.0AC-3L-I196.5300-------000N 0.0 0.0000000 YYT Survey Point NAV CHECK

U0003V00021117102067+47.6203-052.7398+46.2106-052.2095 86.75166.82 0.0VU-3L-I156.8909UNK----000N 95.4 8.5000000KOMETIK 316214000 VCRT AIS TARGET

U0004V00031117101960+47.6203-052.7398+47.5680-052.7010 3.51153.38 0.0VU-3L-I195.7709UNK----000N 0.0 0.0000000MAERSK PLACENTIA 316197000 VCYQ AIS TARGET

U0005V00041117101915+47.6203-052.7398+47.2948-052.5665 20.92158.77 0.0VU-3L-I190.5009UNK----000N266.2 8.4000000 355844000 AIS TARGET

U0006V00051117101956+47.6203-052.7398+47.5607-052.7015 3.89156.53 0.0VU-3L-I196.0109UNK----000N138.4 0.0000000LOUIS M LAUZIER 316003090 CG3159 AIS TARGET

H0007A00021117101321+47.6203-052.7398+47.6195-052.7428 0.13248.19 0.0AP-3L-I196.2700-------000N 0.0 0.0000000 RADAR DATA TEST

U0008V00061117101491+47.6203-052.7398+47.5578-052.7065 3.98160.21 0.0VO-3L-I195.9409UNK----000N166.0 0.1000000SIR WILFRED GRENFELL 316051000 CGJY AIS TARGET Dest: ST. JOHN'S ETA: 12-24 00:00

U0009V00071117101636+47.6203-052.7398+47.5618-052.7002 3.86155.39 0.0VU-3L-I196.0209UNK----000N 29.5 0.0000000 316070000 AIS TARGET

N0010A00031117101738+47.6248-052.7452+47.6248-052.7452 0.00 0.00 0.0AON3L-I196.3100-------000N 0.0 0.0000000

U0011V00081117102061+47.6697-053.1189+46.5812-053.0998 65.32179.32 0.0VU-3L-I196.3909UNK----000N 72.0 16.3000000 316001380 AIS TARGET

U0012V00091117102068+47.6697-053.1244+47.7747-054.0155 36.52279.93 0.0VU-3PSI145.8609UNK----000N114.0 0.0000000 316002280 AIS TARGET

I need to get the mid out of the first line above and then use that do delete the older real-time data from the rt_edt table. All other rows below that need to be inserted into the rt_edt table. And then all other subsequent updates do the exact same thing as above.

As for your point, I thought that for the cursor, it would keep the 'mid' value in memory until the cursor completes so that it can be used again for the final updates into our final 'target' table which is the final resting place for the data. Any more thoughts?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I see what's hapenning now.. but if the @mid variable is set only once, then the delete from the table will delete data only once. This is because it will delete all the data related to that value and that after the first delete, there won't be anything left to delete... Maybe you need to add another where condition to that delete if you need multiple deletes??

Cory Slaney SSCrazy Points: 2459 More actions · Answer 6

Thanks Remi. I know that I need to delete all the data from the rt_edt table prior to going into the cursor to ensure that there is no duplicate target data (delete from statement). I know that I have to select from the cursor first to get the string from the rt_navstaging table to determine the 'mid' value for the delete but at that point, as you have stated above, it will only perform the delete for the first selection from the cursor because there will be a match on the 'mid'. And that is the only unique identifier for the records coming in. Maybe I could execute another stored procedure from within the stored procedure above to perform the delete operation. Any further suggestions?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Maybe I gon't have the right idea of what's happening.

Do you have multiple blocks of codes like the one you sent me?

If this is the case I would suggest that you run that stored proc in debug mode to see where it's failing.

Cory Slaney SSCrazy Points: 2459 More actions · Answer 8

You do have the right idea! Your comments above have prompted me to do further testing. I just have to wait for the next real-time update to our application to get some data. I suspect that the delete has been failing due to the fact that the 'mid' value is only being written to the rt_nav table only once and therefore, all subsequent targets for the current real-time update are being inserted into the table without the mid. Therefore, when the real-time update takes place again, only those rows from the last update with a 'mid' value are being deleted, while those without are being orphaned in the database. Does that make sense? Is there a way for me to do a delete from the rt_edt table before going into the cursor using the 'mid' value from the first line?

For example, from this line:

BKNL05-271C-GMRS

I only need to get the mid, which in this case is 'BKNL05-027' for the purposes of the delete. Then all other records below are the actual targets that need to be inserted into the rt_nav table.

For example, the following: