Wednesday, March 28, 2012

why it works one place and not the other

I'm copying data in a table and replacing one columns (auto-incremented) value. So I wrote my stored procedure as follows:
@dotnet.itags.org.Description nvarchar(100),
@dotnet.itags.org.Start int,
@dotnet.itags.org.EffectDate smalldatetime,
@dotnet.itags.org.Period int
AS
INSERT INTO Period (CompanyID, FacilityID, [Description], StartMonth, EffectiveDate)
SELECT CompanyID, FacilityID, @dotnet.itags.org.Description, @dotnet.itags.org.Start, @dotnet.itags.org.EffectDate
FROM Period WHERE PeriodID = @dotnet.itags.org.Period
GO

So that worked fine and all it inserted it no problem, so I go to do it to another table and it won't work, I put the following:
@dotnet.itags.org.Period int
AS
INSERT INTO Items(CompanyID, FacilityID, PeriodID, CategoryID, ItemID, SeqNbr, [Description], Notes)
SELECT CompanyID, FacilityID, PeriodID, CategoryID, ItemID, SeqNbr, [Description], Notes
FROM Items WHERE PeriodID = @dotnet.itags.org.Period
GO

This table I'm copying about 15 things and when I run the stored procedure in Query Analyzer it tells me the following:
Violation of PRIMARY KEY constraint 'PK_Items'. Cannot insert duplicate key in object 'Items'.
The statement has been terminated.

Please help!!!!!

Just at first glance it would seem that ItemID is your Primary Key and you are trying to insert a new record with an ItemID that is being selected from an existing record, hence the duplicate PK error.
I have that column set to auto-increment as well--so I'd assume it just assign my next value to itemid.

0 comments:

Post a Comment