Duplicate records in a Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicate records in a Table

Author

Topic

udatharamesh
Starting Member

2 Posts

Posted - 2005-05-02 : 08:34:58

Hi There,
I have one stored procedure (used to insert/update a record) performing following steps.

Parameter: PK_CatId

Step#1: Check if there is any record in Table_1 for PK_CatId
Step#2: If there is any record, update Table_1 where CatId=PK_CatId
Step#3: If Not, Insert into Table_1 for PK_CatId

Note: Table_1 is not having any primary key on PK_CatId

In a web application scenario when there is two requests are coming to insert one PK_CatId(value is same for both the requests) it is inserting 2 records for the same PK_CatId. Because when in first request step1 is executed no records were found and it is about to insert record and that time step#1 of second request is performed so request 2 also inserting the record.

I didn't not put any primary_key to this table because both the data is very important for me and in the above scenario if primary key violation exception occurs I will be losing the second request data.

Please provide me a solution for this...

Thank you in advance.
- Ramesh U

Ramesh Udatha

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-02 : 09:35:56

quote:
Originally posted by udatharamesh

Please provide me a solution for this...

Sure, put a primary key on the table


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(10))
GO

INSERT INTO myTable99(Col1, Col2) SELECT 1,'a'
GO

CREATE PROC mySproc99 @Col1 int, @Col2 char(1)
AS

	UPDATE myTable99 SET Col2 = @Col2 WHERE Col1 = @Col1
	IF @@ROWCOUNT = 0 
		INSERT INTO myTable99(Col1, Col2) SELECT @Col1, @Col2
GO

EXEC mySproc99 2,'b'
GO

SELECT * FROM myTable99
GO

EXEC mySproc99 1,'c'
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP PROC mySproc99
DROP TABLE myTable99
GO

Brett

8-)

udatharamesh
Starting Member

2 Posts

Posted - 2005-05-20 : 02:50:13

Hi Brett,

Thankyou for your reply. I followed the same steps except maintaining the Transaction in Stored procedure.

I got the solution now. I have done like this.

put the primary key on the column

Step1: Insert statement to insert record
Step2: Iff error code is 2627 then run update statement.

error code : 2627 stands for primary key failure.

insert INTO Category values (@iCatId, @CatStatus)
set @iERROR=@@error
if @iERROR = 2627
BEGIN
Update Category set Status=@CatStatus Where catid=@icatid
return(0)
END

Ramesh Udatha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources