I have a table where are 4 primary keys, I want to stop procedure if there exists particular entries. ** DECLARE curDis cursor FOR SELECT DisID FROM #DISTRICT OPEN curDis PRINT @dis FETCH NEXT FROM curDis INTO @dis WHILE @@FETCH_STATUS = 0 BEGIN set @sqlQuery = N' INSERT INTO READINGS (PeriodYear,PeriodID,Tariff,ElDeb,Device,Dis)** this is example How do I add records into table --- [Edit] HI again, what do you think about this solution? DECLARE @RECcount INT SET @RECcount=(SELECT ISNULL(COUNT(Device),0) FROM READINGS WHERE PeriodID = @BP AND PeriodYear = @BY AND Flag = 'S') IF @RECcount <> 0 RETURN; ELSE Column flag I added additionally to indicate records from which server I'm getting.
A few options spring to mind to prevent duplicate entries: - Create a unique index covering the fields for which you wish to prevent duplicates - create a trigger on the insert statement that checks the incoming data against what's already there. If it is already there, then raise an error - Wrap the insert statement up into a stored procedure that first checks to see if the data is there before inserting it - Investigate the [MERGE] statement rather than the INSERT statement - this can be computationally expensive There are probably several other ways... :
Thomas has suggested some potential solutions, what I would ask is why are you using a cursor? Its often used when there is a better 'Set-Based' solution and that could itself remove the chance of duplicates, due to the way that joins may be used. Can you explain a little more about the process you are working on and we may be able to expand our suggestions to improve a lot more than just preventing the insertion of duplicates.
I would have to agree with Fatherjack here - if all you want to do is stop on duplicates, i.e.a unique violation, get the whole query into one SQL statement, and it will simply rollback the whole work if it hits the violation. Alternatively if it can't be done in one SQL statement, batch it into a transaction with some error handling (like a try-catch block), and run it that way