Pulling my hair out with my SQL Statement!!!!

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

Transact-SQL (2000)

Pulling my hair out with my SQL Statement!!!!

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2006-08-01 : 16:52:02

I have 2 problems:

1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run

2) The first insert doesn't insert anything, although I get no errors

ALTER PROCEDURE Categories_I

3) I also get this when trying to run just the first 2 insert statements together

Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'

AS
BEGIN

DELETE Category
DBCC CHECKIDENT ('Category', RESEED, 0)

ALTER TABLE [Category] DROP CONSTRAINT Category_Category_FK1

SET IDENTITY_INSERT Category ON

INSERT INTO Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

SET IDENTITY_INSERT Category OFF

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END

ALTER TABLE [Category]
ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY
(
[ParentCategoryID]
) REFERENCES [Category] (
[CategoryID]
)

END

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-02 : 04:47:10

Constraints are meant to be permanent so sql server doesn't expect them to be changing while a batch is run.
To get this to work you would [probably have to do everything in dynamic sql so that everything is a separate batch.
Better though is to think of another way to do it - this looks like an underlying design problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-03 : 09:58:58

quote:
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'

Ok, this is an important error, and you are trying to circumvent it? I really don't understand.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources