0

Would like to know what would be the best way to check if the records exist before INSERT/UPDATE or DELETE the record.

I assume MERGE would be an optimal way but i have read that i might not be the best solution as they have bugs and also affect performance (Now these articles that i have read could be older issues)

We are using SQL SERVER 2016. So any content relevant to this edition could be helpful?

I also think, checking the database based on the PK and based on the value returned that i can either perform an insert or update

for example If ( count from the table is 0) then Insert else update.

I am just trying to find the best way to handle our daily transactions. Our application processes an average of 200,000 records/hour.

Thanks

alangilbi
  • 321
  • 5
  • 17
  • 1
    Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – Ken White Aug 13 '18 at 00:16
  • Could you clarify _I assume MERGE would be an optimal way but i have read that i might not be the best solution as they have bugs and also affect performance_ – qxg Aug 13 '18 at 06:38
  • I assume for my scenario the best way is to use a MERGE statement but then i have read about MERGE having issues and also could cause a performance impact. so i am asking for solutions – alangilbi Aug 13 '18 at 13:15
  • It is a possible duplicate .. Thanks for pointing out. But I think the confusion still exists as to which one would be better. Just looking for some real experiences. – alangilbi Aug 13 '18 at 13:19

1 Answers1

0

I would create a stored procedure that checks for the existence of the record before insertion, such as (assuming you have automatically generated identities for the primary key)

CREATE PROC PR_Insert_Something @FirstName @LastName AS BEGIN BEGIN TRAN IF NOT EXISTS (SELECT * FROM TABLE A WHERE FirstName = @FirstName AND LastName = @LastName) BEGIN INSERT INTO TABLE A (FirstName,LastName) VALUES(@FIRSTNAME,@LASTNAME) COMMIT TRAN END ELSE BEGIN RAISERROR() <-- Look up RAISERROR if unsure what to do here ROLLBACK TRAN END END GO;

Always better to preform SQL operations dynamically from the application anyways.

Karl.T
  • 11
  • 1