asked
Prevent duplicate entries
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.
Hi @agron - I have edited your question to include the extra info that you supplied, I hope it is OK with you but it makes more sense to others reading the question at a later date ..
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][1] statement rather than the INSERT statement - this can be computationally expensive There are probably several other ways... [1]:
http://technet.microsoft.com/en-us/library/bb510625.aspx
I just found a solution, I just added a column flag, now i'm checking PeriodId, Periodyear and Flag, All this problem was because they are 2 procedures, selecting from different sources and adding data in same table for same period and year. Thanks you again for you effort
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.
Oh quite - even going on what @agron says in comment to my answers, I still wonder quite why he's going about it like that...
I will provide you with some more information about my query, First step: Cursor is used because I have to access several different servers to select data from. In cursor is defined a server name, Every Server has database with exactly same structure, From this servers I have to select Records where exists also in Production server. Step two: For records which are already entered in table READINGS, I have to get readings form another two different systems and update some columns on it. This is quiet difficult also for explanation but all this mess is because I cannot change anything in existing system, I found it like that and I have to continue like is it.
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
