Inserting data into an SQL table with a primary key. For cheats - allow insertion or select first?
For a table such as:
CREATE TABLE dbo.MyTestData (testdata varchar(50) NOT NULL)
ALTER TABLE dbo.MyTestData WITH NOCHECK ADD CONSTRAINT [PK_MyTestData] PRIMARY KEY CLUSTERED (testdata)
And given that we want to get a unique list of "testdata" when we have finished collecting items to be added from the list of external data with known duplicates ... When executing an insert stored procedure, if the procedure should be written to test for existence or if it just make a mistake? What's the most common practice? I've always done the existence test, but discussed this last night ...
CREATE PROCEDURE dbo.dmsInsertTestData @ptestdata VarChar(50)
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT testdata FROM dbo.MyTestData WHERE testdata=@ptestdata)
BEGIN
INSERT INTO dbo.MyTestData (testdata ) VALUES (@ptestdata)
END
RETURN 0
or just capture / ignore PK violation errors while doing this?
CREATE PROCEDURE dbo.dmsInsertTestData @ptestdata VarChar(50)
AS
SET NOCOUNT ON
INSERT INTO dbo.MyTestData (testdata ) VALUES (@ptestdata)
RETURN 0
source to share
Your error checking (eg "IF NOT EXISTING ...") may or may not work because there is a potential race condition (if another transaction inserts a record after your IF NOT EXISTS statement, but before your INSERT statement).
Therefore, if you check before, you should code the INSERT statement as if it had failed.
If you want to check , and (instead), it depends on you and on your user interface.
source to share
My concern with the first example you provided is that it doesn't return an error to the user. It can be made fixed, but I wouldn't use it if it didn't return an error.
If your concern between the two possible possibilities is performance on large tables, I suggest you test both of them and see if it is significantly faster than the other. If the if choice is particularly tricky, and the insertion needs to happen lately in most cases, it's possible that just letting it fail will be faster in most cases. If, on the other hand, the likelihood of bad input is high and if is relatively uncomplicated, as shown here, then another process might be better. But only real testing your real data and data structure and your real queries can tell you which one is better for performance, as it can be different from each other compared to situations.
source to share
I believe it depends on the nature of the stored procedure. Basically, you should handle errors if you have something to do with them (or encapsulate them for procedure clients) and leave them common if you have nothing to do with them and cannot make them friendlier to other layers of the application. ...
If the stored procedure is designed to insert raw data, I think it should leave the application to handle possible errors. If the stored procedure is designed as an abstraction layer (and performs a specific task as opposed to running a specific statement ) and can either handle the error, or do something about it, or can communicate it gracefully (e.g. well-defined error codes) to the application, it should do it. Otherwise, it should be up to the application to make sure it is not inserting duplicate data and not the database (the database has already applied this with primary keys).
source to share
To be user friendly, it is often advisable to perform a SELECT and if the record already exists, offer the user the ability to view and / or edit it.
For example, if a user adds a new customer record, they might want to view information that has already been displayed for that Customer. They may have additional information to add to the record, such as a phone number.
In this scenario, not adding an entry is less useful than offering the ability to view an existing duplicate.
source to share