IF exists UPDATE ELSE INSERT problem

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)

IF exists UPDATE ELSE INSERT problem

Author

Topic

Kooba
Starting Member

5 Posts

Posted - 2004-02-18 : 16:39:22

Hi,

I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).

I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.

Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I’m not sure how should I go about that problem. This is my stored procedure:

DECLARE @CatNo INT, @CatName varchar(10)
SET @CatNo = 2
SET @CatName = 'bracket'

IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN

UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid

END
ELSE
BEGIN

INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName

END

SET @CatNo = 3
SET @CatName = 'cable'

IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN

UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid

END
ELSE
BEGIN

INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName

END
(... Goes for another 37 categories)

Thank you for help.

Kooba

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-18 : 18:23:47

I think you could streamline this with two Triggers.

1st on insert insert into dbo.procductcatagory

2nd on update Update dbo.procductcatagory

I would give you the code but I am haveing a hard time with the relationship between your tables.

Jim
Users <> Logic

Kooba
Starting Member

5 Posts

Posted - 2004-02-19 : 10:43:47

I have not set relationship between the tables yet but i think i should make 'ProductID' in 'ProductCategory' table a foreign key to 'uid' in 'Products' table.

Kooba

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-19 : 11:11:25

Ok first I suggest you make it easy on yourself and make a cross reference table to you can have a nice clean relationship.

CatRefTable

CatName CatagoryId

Bracket 2
cable 3
...

Then you can use one Case statement for your Update / insert Decision

Jim
Users <> Logic

Kooba
Starting Member

5 Posts

Posted - 2004-02-19 : 11:37:37

I'm using packaged e-commerce solution called StoreFront I can not make to many drastic changes to it, problem is that as they allow you to update your inventory by importing CSV file, that update doesn't populate any further changes to for example categories table. I was hoping that I can get around with it by running this stored procedure after CSV upload.

Can you tell me more about the Triggers you mentioned before?

thank you so much for your help.

Kooba

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-19 : 11:45:44

Ok First a little background.

What version of SQL server do you have.

Do you have admnistration Rights to the server?

Do you have Enterprize Manager Installed?

Do happen to have MS Access installed?

Jim
Users <> Logic

Kooba
Starting Member

5 Posts

Posted - 2004-02-19 : 11:56:28

I'm running MS SQL 2000, Enterpirce Manager installed, I have All administrator rights to it, and fairly good understandig of SQL development ;-). Package i purchased comes with full source code.

Kooba

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-19 : 12:07:01

Do you Have Books Online Installed?

If not go here.

[url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]

In addition to SQL Team this will be one of your most usefull tools.

The Changes I am talking about will all be on the back end and should have no effect on your front end.

Jim
Users <> Logic

Kooba
Starting Member

5 Posts

Posted - 2004-02-25 : 11:54:00

Jim,

Thank you so much!
All I wanna say is Triggers Rulez!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources