Check for duplicates before inserting a record

we should use real table but we will use temporary table at this time for the sake of testing.

DECLARE @items table (ID VARCHAR(2), [DESCRIPTION] VARCHAR(50))
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('a','apple')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('b','bag')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('c','cat')

declare our input variable

DECLARE @inputID varchar(max) = 'c'
DECLARE @inputDescription varchar(max) = 'cat'

Check the record if exists. If the record exists, raise an error

IF EXISTS(	SELECT	1
			FROM	@items 
			WHERE	ID = @inputID 
		) BEGIN
		RAISERROR ('Record Already Exists', 11, 1)
END

let's insert and check our new record

INSERT INTO @items 
VALUES (@inputID, @inputDescription)

SELECT	*
FROM	@items

let's wrap it up with a TRY CATCH

DECLARE @items table (ID VARCHAR(2), [DESCRIPTION] VARCHAR(50))
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('a','apple')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('b','bag')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('c','cat')


BEGIN TRY

	DECLARE @inputID varchar(max) = 'c'
	DECLARE @inputDescription varchar(max) = 'cat'

	IF EXISTS(	SELECT	1
				FROM	@items 
				WHERE	ID = @inputID 
			) BEGIN
			RAISERROR ('Record Already Exists', 11, 1)
	END


	INSERT INTO @items 
	VALUES (@inputID, @inputDescription)


	SELECT	*
	FROM	@items

END TRY
BEGIN CATCH
	
END CATCH

Let's make it transactional. Rollack all changes on error

BEGIN TRY
	
	BEGIN TRANSACTION
		
	DECLARE @inputID varchar(max) = 'c'
	DECLARE @inputDescription varchar(max) = 'cat'

... ... ...

	SELECT	*
	FROM	@items

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH

Let's re-raise the error

BEGIN CATCH
	ROLLBACK TRANSACTION
	DECLARE @errorMessage NVARCHAR(MAX),  @errorSeverity INT,  @errorState INT
	SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE()
	RAISERROR(@errorMessage, @errorSeverity, @errorState)
END CATCH

And it's done

DECLARE @items table (ID VARCHAR(2), [DESCRIPTION] VARCHAR(50))
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('a','apple')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('b','bag')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('c','cat')


BEGIN TRY
	
	BEGIN TRANSACTION
		
	DECLARE @inputID varchar(max) = 'c'
	DECLARE @inputDescription varchar(max) = 'cat'

	IF EXISTS(	SELECT	1
				FROM	@items 
				WHERE	ID = @inputID 
			) BEGIN
			RAISERROR ('Record Already Exists', 11, 1)
	END


	INSERT INTO @items 
	VALUES (@inputID, @inputDescription)


	SELECT	*
	FROM	@items

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	DECLARE @errorMessage NVARCHAR(MAX),  @errorSeverity INT,  @errorState INT
	SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE()
	RAISERROR(@errorMessage, @errorSeverity, @errorState)
END CATCH

Back To Programming & Software Support