Clash Royale CLAN TAG#URR8PPP

Best Solution for checking DB before inserting / updating or deleting

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

1 Answer
1

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.