Cursor - Multiple Writes

Question

Post reply

Cursor - Multiple Writes

Colin Betteley

Hall of Fame

Points: 3095
More actions
April 20, 2006 at 3:23 am

#112765

Morning,
I have a cursor which joins 3 tables. I then examine each line and write to a DIFFERENT table depending on what each attribute value is. Part of the code is shown below.
IF @Stream1 <> '999'
      BEGIN
         INSERT INTO cvb_PDM_Streams (pdmID, StreamId) values (@pdsFileName_AUTO, @Stream1)
   END

   IF @Stream2 <> '999'
      BEGIN
         INSERT INTO cvb_PDM_Streams (pdmID, StreamId) values (@pdsFileName_AUTO, @Stream2)
   END

My problem is that after one line has been written to the table cvb_PDM_Streams I am told that all future attempted writes are invalid as I am entering a duplicate primary key - (Violation of PRIMARY KEY constraint 'PK_cvb_PDM_Streams'. Cannot insert duplicate key in object 'cvb_PDM_Streams'.
The statement has been terminated.)
The table I am writing to has two attributes and together they make up a joint PK. Looking at the data produced by the join, there is not any duplicate data and therefore a duplicate PK is not possible. It is as if SQL was trying to write the same line twice.
Thoughts welcomed. Thanks.
Colin

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What are the values of @Stream1 and @Stream2

_____________
Code for TallyGenerator

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 2

Doh! As soon as you post, the fog lifts. I had an END before FETCH NEXT rather than after it! Seems to be working OK now. Thanks anyway.

Colin

PS the values are all INT

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

1. in the code posted you are not writing to 2 different tables.

2. try to stay away from cursors....(bad, bad, bad...search here for reasons why this is true 99.99% of the time). you would be far better off writing 2 different select statements on the lines of.

insert into tablea (col1,col2,col3, etc) select acol1, acol2, acol3, etc from intablea where @instream = '999'

and then

insert into tableb (col1,col2,etc) select bcol1, bcol2, etc from intableb where @instream <> '999'

cursors are like using tweezers to put sugar grains in a cup of tea....far easier to use a spoon!