Unique constraint stopping stored proc from workin

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)

Unique constraint stopping stored proc from workin

Author

Topic

hearnie
Starting Member

49 Posts

Posted - 2005-07-25 : 10:27:18

Hi there,

I have a stored procedure that I have set to run every hour. In this stored proc I bulk load into some table and then use this to load into one of my main tables. when loading if the stored procedure comes across a unique key constraint and there is a duplicate that its trying to insert it then fails and the task does not complete.

Is there a way to ignore duplicate entries so that the procedure continues and finishes successfully.

H.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-25 : 11:30:41

Clean the data before you try and load it? Or, ram it into your staging table that has no constraints, THEN clean it, THEN move it to your main table.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

hearnie
Starting Member

49 Posts

Posted - 2005-07-25 : 12:23:09

By clean the data, do you mean add a part into the stored procedure which will delete the duplicate entries that already exist in my live table?

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 13:09:09

you shoud show the source of your SP.
Actually, transaction do not fail to proceed if it comes across unique constraint. Try this

declare @t table (i int primary key)
begin tran
insert into @t values(1)
insert into @t values(1)
insert into @t values(2)
select * from @t
commit

and you'll see that table @t will contain 2 values, unless set xact_abort option is set on

Ravenn
Starting Member

7 Posts

Posted - 2005-07-25 : 14:15:59

Awww. You can add WHERE clause to whatever selects the data for insertion.
WHERE MyCoolKeyColumn NOT IN (SELECT MyCoolKeyColumn FROM MyBigTable).

And, if you actually need newer data rather than the older in case of duplicates, you will need to add code to update existing values that are duplicate

hearnie
Starting Member

49 Posts

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

yeah, the problem is in stored procedure I have an update stmt, then after I have an insert statement so I thought the update would have caught the condition and updated anything old and then the insert would have done anything that was new.

I'll post some of the stored proc later and explain.

thanks

H.

hearnie
Starting Member

49 Posts

Posted - 2005-07-26 : 05:55:11

ok so here is where the problem is happening. There is 2 temp tables which gets bulk loaded from a file with all values needed. Then there is table A which gets updated from TableA(the bulk loaded table). The table A which holds the real data has a unique constraint on desig_watt_label. When this runs its failing the stored procedure task due to this unique constraint violation.

Can anyone help?

--update the TableA table based on joining existing TableA values with TempDataA
UPDATE A SET
A.ID = tmpA.ID, A.pro = tmpA.pro, A.desig = tmpA.desig, A.watt = tmpA.watt, A.label = tmpA.label,A.appname = tmpA.appname,A.description = tmpA.description
FROM TableA A
JOIN TempDataA tmpA ON A.desig = tmpA.desig AND A.watt = tmpA.watt AND A.label = tmpA.label

--insert into the TableA table based on joining existing TableA values with TempDataA
INSERT TableA(ID,Pro,Design,Watt,Label,Appname, Description)
SELECT DISTINCT tmpA.site, tmpA.pro, tmpA.desig, tmpA.watt, tmpA.label, tmpA.appname, tmpA.description
FROM TempDataA tmpA
LEFT JOIN TableA A ON tmpA.desig = A.desig AND tmpA.watt = A.watt AND tmpA.label = A.label
LEFT JOIN TempDataB tmpB ON tmpB.desig = tmpA.lot AND tmpB.watt = tmpA.watt
WHERE A.desig IS NULL AND A.watt IS NULL AND A.label IS NULL

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-26 : 09:02:36

something like

UPDATE A SET
A.ID = tmpA.ID, A.pro = tmpA.pro, A.desig = tmpA.desig, A.watt = tmpA.watt, A.label = tmpA.label,
A.appname = tmpA.appname,A.description = tmpA.description
FROM TableA A
JOIN TempDataA tmpA ON A.desig = tmpA.desig AND A.watt = tmpA.watt AND A.label = tmpA.label
where tmpA.id not in(select tempA.id from A,tempA where A.desig = tmpA.desig AND A.watt = tmpA.watt AND A.label = tmpA.label)

hearnie
Starting Member

49 Posts

Posted - 2005-07-28 : 10:30:20

The problem is not actually in the updates statement but rather in the Insert statement. the insert is trying to insert a value that is already there and getting a Unique key violation.
I included the Update also because I was under the impression that because the update would get executed first that it would have done any of the updates and committed before the insert got executed and so would not run into unique key violations?

H.

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 11:20:04

>>I was under the impression that because the update would get executed first that it would have done any of the updates and committed before the insert got executed and so would not run into unique key violations?

Are you trying to say that UPDATE cannot trigger (or "run into", don't know how to say that in english) unique key violation ? If so, you are wrong, because for SQL Server there is no difference what caused the violation - insert or update.

hearnie
Starting Member

49 Posts

Posted - 2005-07-28 : 11:52:11

I suppose what Im saying is that Im assuming it is the INSERT that is failing because the error specifically says, Unique Key violation when inserting into table.

H.

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 12:17:01

:)

Try this :
-------------------------------
use pubs
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO

CREATE TABLE [dbo].[Table2] (
[field1] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX Idx1 ON Table2 (field1)
GO

insert into table2 values(1)
insert into table2 values(2)
update table2 set field1=1 where field1=2

------------------------------------------
(be careful, if you have Table2 in Pubs it will be dropped and recreated)

You will get the following messages:
-----------------------------------------
(1 row(s) affected)

(1 row(s) affected)

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Table2' with unique index 'Idx1'.
The statement has been terminated.
--------------------------------------

you see?

nadejda18
Starting Member

7 Posts

Posted - 2005-07-28 : 12:41:04

Your data contains dublicate records in the context of your primary key desig_watt_label. It's bad, and you should find these records. The problem is what concrete values from that dublicates you need to store.

To avoid Unique Key violation you need to get an insert select with unique rows in context of your primary key - and use just DISTINCT give you unique rows in context of ALL fields.
You can use

group by tmpA.desig, tmpA.watt, tmpA.label

in your insert select. For the fields not included in GROUP BY you have to use aggregate functions like MAX, SUM, i.e.

hearnie
Starting Member

49 Posts

Posted - 2005-07-28 : 12:57:54

quote:
Originally posted by Stalker

something like

UPDATE A SET
A.ID = tmpA.ID, A.pro = tmpA.pro, A.desig = tmpA.desig, A.watt = tmpA.watt, A.label = tmpA.label,
A.appname = tmpA.appname,A.description = tmpA.description
FROM TableA A
JOIN TempDataA tmpA ON A.desig = tmpA.desig AND A.watt = tmpA.watt AND A.label = tmpA.label
where tmpA.id not in(select tempA.id from A,tempA where A.desig = tmpA.desig AND A.watt = tmpA.watt AND A.label = tmpA.label)

The only problem I see what the above is that where it says

WHERE tmpA.id not in (...)

The actual tmpA table does not have any id's, it is just a table where all the information gets bulk loaded into from files. So it has no id to do the where clause on.

H.

hearnie
Starting Member

49 Posts

Posted - 2005-07-28 : 13:01:57

quote:
Originally posted by nadejda18

Your data contains dublicate records in the context of your primary key desig_watt_label. It's bad, and you should find these records. The problem is what concrete values from that dublicates you need to store.

To avoid Unique Key violation you need to get an insert select with unique rows in context of your primary key - and use just DISTINCT give you unique rows in context of ALL fields.
You can use
group by tmpA.desig, tmpA.watt, tmpA.label
in your insert select. For the fields not included in GROUP BY you have to use aggregate functions like MAX, SUM, i.e.

The data in the temp table which is the table which contains bulk loaded data from a file, this table may well contain duplicate data. Its ok for this table to have this.

I thought that by using the update and insert statements provided above that it should load the data accordingly and because I tried the update statement before the insert that it would not run into the unique key violation. Im still stuck as to what I can change in the sql code above to make it ignore values that are already meeting the constraint of desig_watt_label

H.

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 13:02:49

>>The actual tmpA table does not have any id's,

how it's connected with your words before:

>>Can anyone help?

>>--update the TableA table based on joining existing TableA values >>with TempDataA
>>UPDATE A SET
>>A.ID = tmpA.ID, A.pro = tmpA.pro, A.desig = tmpA.desig,...
(Posted - 07/26/2005 : 05:55:11 )
I see tmpA.ID there !

hearnie
Starting Member

49 Posts

Posted - 2005-07-28 : 13:10:15

sorry that was misleading on my part. That id is not a unique identifier. treat the statement as if that was not there.

nadejda18
Starting Member

7 Posts

Posted - 2005-07-28 : 14:09:24

Probably, something like this:

INSERT TableA(ID,Pro,Design,Watt,Label,Appname, Description)
SELECT DISTINCT tmpA.site, tmpA.pro, tmpA.desig, tmpA.watt, tmpA.label, tmpA.appname, tmpA.description
FROM TempDataA tmpA
LEFT JOIN TableA A ON tmpA.desig = A.desig AND tmpA.watt = A.watt AND tmpA.label = A.label
LEFT JOIN TempDataB tmpB ON tmpB.desig = tmpA.lot AND tmpB.watt = tmpA.watt
WHERE A.id is not in 
(select A1.id FROM TableA A1, TempDataA tmpA1 
WHERE A1.desig = tmpA1.desig 
AND A1.watt = tmpA1.watt 
AND A1.label = tmpA1.label)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources