April 20, 2006 at 3:23 am
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
April 20, 2006 at 3:33 am
What are the values of @Stream1 and @Stream2
_____________
Code for TallyGenerator
April 20, 2006 at 3:54 am
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
April 21, 2006 at 3:16 am
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply