Avoiding error in INSERT of non-unique row

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)

Avoiding error in INSERT of non-unique row

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-03 : 13:24:11

I've got a stored proc that occasionally get's data that is non-unique. I want to insert the data, get the error, clear the error, and return to the user without indicating any error.

How'ja do this?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 13:36:31

[code]
USE Northwind
GO

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

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

-- Gains Control but still throws an Error, and only requires 1 Database Access
INSERT INTO myTable99(Col1,Col2) SELECT 1,'A'
IF @@ERROR <> 0
SELECT 'Error'
GO

-- Gains Control but does not throw an error, but requires 2 Databases Accesses
IF NOT EXISTS(SELECT * FROM myTable99 WHERE Col1 = 1)
INSERT INTO myTable99(Col1,Col2) SELECT 1,'A'
ELSE
SELECT 'Error'
GO

SET NOCOUNT ON
DROP TABLE myTable99
GO

[/code]

You hit your head on a rock kayaking? Or is the water to cold still?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-03 : 14:00:50

I'm waiting for warmer weather. Just dropped 40-65 this week.

The first example (executing a simple SELECT 'abc') won't clear the error will it? Aren't errors pushed up on a stack somewhere?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 14:21:55

Sam,

Cut and paste the code and execute it...you'll see.

The error will raise at the end of the batch regardless of what you do, but at least you'll have control.

The second one intercepets the "error", before it happens and doesn't raise.

Personally, if the application caise the dup key, I want the application to handle the error. I catch it, and regain control, mostly so I can exit the sproc.

I'm not a fan of the second method...the application should be constructed that it "knows" not to try request an insert of a dup key.

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-03 : 15:02:59

It is fairly eash to check for a duplicate row in the INSERT statement, and just not insert it if it already exists:


set nocount off
declare @myTable99 table (Col1 int not null primary key, Col2 char(1))

print 'Insert first Row'
insert into @myTable99 (Col1,Col2)
select Col1 = 1, Col2 = 'A'

print 'Try to insert duplicate row'
insert into @myTable99 (Col1,Col2)
select
	a.Col1, a.Col2
from
	(select Col1 = 1, Col2 = 'A') a
	-- Self join to check for duplicate row
	left join
	@myTable99 b
	on a.Col1 = b.Col1
where
	-- Verify row to be inserted does not exist
	-- by making sure the primary key does not exist
	b.Col1 is null


Results:
Insert first Row

(1 row(s) affected)

Try to insert duplicate row

(0 row(s) affected)

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 15:28:38

Hey Kernel,

How do you error handle that?

IF @@ROWCOUNT = 0?

What if it's a set based insert

And a follow up question...

Do you really do that?

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-03 : 17:49:10

I did not include any error handling logic in the post. I was just showing the logic of using a left join on an insert to not attempt to insert the row if a duplicate primary key exists in the table. Of couse, in a production stored procedure I would check @@error and @@rowcount, and report an error on failure.

It already is a set based insert; it's just that in the example the set consists of a single row. For a multi-row inset, you just do the same left join, and verify that the primary key of the right table is null. I am assuming that there is an actual primary key.

Or are you worried about the posibility of only some rows being inserted from a multi-row set? The possibility exists, but if that is a good or a bad thing depends on the application.

The reason I like to do it this way, it that the check happens within the transaction of the insert statement, so there is no gap in time between the check and the insert.

And yes, I actually do this in real production applications, and I haven't seem it cause a problem. Is there something that you see wrong with it?

quote:
Originally posted by X002548

Hey Kernel,

How do you error handle that?

IF @@ROWCOUNT = 0?

What if it's a set based insert

And a follow up question...

Do you really do that?

Brett

8-)

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-04 : 09:05:10

quote:
Originally posted by Michael Valentine Jones

Or are you worried about the posibility of only some rows being inserted from a multi-row set?

Bingo. Aren't you concerned?

quote:

The possibility exists, but if that is a good or a bad thing depends on the application.

How is the application layer suppose to be concerned with this? Especially with the a set based INSERT? How for example are you suppose to know the quantity of the set?

Or am I being to dense...again...

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-04 : 09:12:45

For what it's worth, my application inserts one row. It shouldn't ever duplicate, but if it does, nothing breaks. With a clustered index, it'll kick out the duplicate. I just don't want to return an error to my app mon!

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-04 : 13:07:26

Insert into Table1 (Col1, Col2, Col3)
Select Col1, Col2, Col3 From Table2 T2 -- Col1 is the unique key
Where Not Exists (Select * From Table1 T1
Where T1.Col2 = T2.Col2)

This adds a bit of expense (but not much), and will eliminate duplicate keys being inserted in Table1 from Table2.

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-04 : 13:10:38

OOps - typo (typed "Col2" instead of "Col1" in predicate)

Insert into Table1 (Col1, Col2, Col3) -- Col1 is the unique key
Select Col1, Col2, Col3 From Table2 T2
Where Not Exists (Select * From Table1 T1
Where T1.Col1 = T2.Col1)

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-04 : 13:33:16

Again, how do you know the potential set of data was fully acted upon?

Does SQL 2005 have an UPSERT?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources