How to "INSERT OR SELECT" in SQL Server 2012 using fewer operations?
I've searched for a question like this for a long time now and I haven't found anything, so if this has been asked before, it will at least serve as a good guide for those who don't know the correct nomenclature.
I want a INSERT INTO
table, if the row doesn't already exist, based on a unique key. That it exists, then I want to get the primary key id of that row.
Imagine a table that contains email addresses:
EmailAddressId(PK) | EmailAddress(UK)
I want to INSERT
add a new email address to this table, but there is a unique constraint on EmailAddress
. Thus, if the new email address is the same as the existing one, it INSERT
will fail. In this case, I want to select an existing one EmailAddressId
from the database for EmailAddress
.
I want to do this in the smallest possible number of operations, assuming collisions are rare.
Thus, I am setting up a block TRY...CATCH
in a stored procedure like this:
ALTER PROCEDURE [dbo].[EmailAddressWrite]
@EmailAddress nvarchar[256]
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @EmailAddressId INT
BEGIN TRY
INSERT INTO EmailAddress VALUES (@EmailAddress)
SET @EmailAddressId = (SELECT SCOPE_IDENTITY())
END TRY
BEGIN CATCH
SET @EmailAddressId = (SELECT EmailAddressId FROM EmailAddress WHERE EmailAddress = @EmailAddress)
END CATCH
--Do some more stuff with the Id now.
COMMIT TRANSACTION
RETURN @EmailAddressId
END
The code above functions and gives the desired result, but the internet makes me think that using TRY...CATCH
this way can be slow ... so I'm not sure if this is the optimal solution.
I only found one solution which is for SELECT
the first and the INSERT
second. This will result in 2 operations almost all the time as I expect very few duplicate email addresses (at least a month or more).
- Is this the optimal solution to achieve 1 operation per
INSERT
and 2 operations perINSERT
failure? - What other solutions can achieve 1 operation on
INSERT
and 2 operations offINSERT
?
If I misused any terminology, please correct it.
source to share
DECLARE @id INT
DECLARE @newid TABLE
(
emailAddressId INT NOT NULL PRIMARY KEY
)
;
WITH t AS
(
SELECT *
FROM emailAddress WITH (ROWLOCK, HOLDLOCK)
WHERE emailAddress = @emailAddress
)
MERGE
INTO t
USING (
SELECT @emailAddress
) s (emailAddress)
ON 1 = 1
WHEN NOT MATCHED BY TARGET THEN
INSERT (emailAddress)
VALUES (emailAddress)
WHEN MATCHED THEN
UPDATE
SET @id = 1
OUTPUT INSERTED.emailAddressId
INTO @newid
;
source to share