INSERT STORED PROCEDURE THAT DOESNT INSERT DUPLICATES

Question

INSERT STORED PROCEDURE THAT DOESNT INSERT DUPLICATES

asm1212

Hall of Fame

Points: 3482
More actions
January 21, 2010 at 9:58 am

#138397

Hey guys,
Ok I am working on a project and cannot figure out how to write a insert sp that will not allow duplicates...
Here is my regular insert statement:
CREATE PROC [dbo].[usp_tblDivisionINSERT]
@DivisionAbbr varchar(15),
@DivisionName varchar(100)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
INSERT INTO [dbo].[tblDivision] ([DivisionAbbr], [DivisionName])
SELECT @DivisionAbbr, @DivisionName
I know I need a WHERE NOT EXISTS but cannot figure it out...I had
WHERE not exists (select * from tblDivision where tblDivision.DivisionAbbr <> @DivisionAbbr)
But that doesnt insert anything...The way this table is designed is i have a divisionID field as the primary key but it is identity in increments of 1...but basing the NOT EXISTS on ID wont work b/c they could still have the same info in the record but different ID's...
So if anyone could help me, it would be greatly appreciated!!!
Thanks,
Adam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You could do it that way:

IF NOT EXISTS (SELECT * FROM usp_tblDivisionINSERT WHERE DivisionAbbr = @DivisionAbbr AND DivisionName = @ DivisionName)

INSERT INTO…

You’ll might have to modify it to handle NULL values (if you can have NULLs). I would also create a unique constraint on the table level to prevent anyone to insert data that I already have without using the stored procedure.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

asm1212 Hall of Fame Points: 3482 More actions · Answer 2

I was wondering about that UNIQUE CONSTRAINT...How does that work? Do you put on a certain field?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

In your case since you need that a combination of columns will be unique, you need to do it on the table level with alter table statement:

ALTER TABLE TableName ADD CONSTRAINT WriteHereConstraintName UNIQUE (WriteHereFirstCol, WriteHereSecondCol)

You can read about it in BOL under create table and alter table.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/