Constraint Error in Stored Proc

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

General SQL Server Forums

New to SQL Server Programming

Constraint Error in Stored Proc

Author

Topic

twhitle
Starting Member

11 Posts

Posted - 2007-12-18 : 13:58:43

This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?

truncate table tbl_rtv_cosmetic_dif
go
insert tbl_RTV_COSMETIC_DIF
select d.fisc_yr
,d.fisc_pd
,d.fisc_wk
,d.fisc_dy
,substring(a.long_sku,1,4) as DeptNo
,substring(a.long_sku,7,3) as VendorNo
,substring(a.long_sku,10,5) as MarkStyleNo
,a.Loc as LocNo
,convert(int,a.RsnCd) as ReasonCode
,min(a.EAN_Nbr) as UPCEANNo
,coalesce(i.own_rtl,0) as OwnedCur
,sum(convert(int,a.units)) as UPCUnits

from november a
inner join generalinfo..tbl_fisc_date d
on a.date = d.cal_date

left outer join itemdata..tbl_item_import_history i
on substring(a.long_sku,1,4) = i.dept
and substring(a.long_sku,7,3) = i.vendor
and substring(a.long_sku,10,5) = i.mrk_style
and d.fisc_yr = i.amc_yr
and d.fisc_pd = i.amc_pd
and d.fisc_wk = i.amc_wk

group by fisc_yr,fisc_pd,fisc_wk,fisc_dy
,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5)
,loc,rsncd,own_rtl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 14:06:24

Most proabably you are inserting a primary key that already exists.

E 12°55'05.25"
N 56°04'39.16"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-12-19 : 05:18:39

You might want to add some pre checking before you try the INSERT ,

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-19 : 05:24:01

Run the query without the insert, ie bring the results back to the query window - order them by the primary key columns - where you find duplicates on these columns is where the problem is.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources