Link to home
Start Free TrialLog in
Avatar of Drevo
DrevoFlag for United States of America

asked on

Insert Record if not exists in child table, then insert that id in parent table - How to?

Hi TSQL experts!

I have two tables, one with a list of product options (table name ProductOptions), and another with a list of product options selected for a specific product (table name ProductOptionMapping). Users can choose options for their products that already exist in ProductOptions, or they can add their own new option. I want to make sure that duplicate ProductOptions don't get created when they enter a new option.

I am trying to create a stored procedure that will check if the option they entered already exists in the ProductOptions, and if not add it. Then it will insert the found (or newly inserted) ProductOptionID into the ProductOptionMapping.

Here are how the tables are organized (simplified for this example):
ProductOptions table:
ProductOptionID (int, pk)
ProductOptionName (nvarchar)

ProductOptionMapping table:
ProductID (int, pk, fk)
ProductOptionID (int, pk, fk)

Of course there's a Products table too...

I have attached some pieced together SQL, though it's obviously not complete or correct. Any help in getting this to work is greatly appreciated.


IF NOT EXISTS (SELECT ProductOptionID FROM ProductOptions WHERE ProductOptionName = @ProductOptionName)
		INSERT INTO ProductOptions (ProductOptionName ) VALUES (@ProductOptionName);
		INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,SCOPE_IDENTITY())
	ELSE
		INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,[Found ProductOptionID here])
	END

Open in new window

Avatar of radcaesar
radcaesar
Flag of India image

did u write this a trigger when updating/inserting? It should be, right?
You're missing your BEGIN and END tags

IF NOT EXISTS (SELECT ProductOptionID FROM ProductOptions WHERE ProductOptionName = @ProductOptionName)
BEGIN
            INSERT INTO ProductOptions (ProductOptionName ) VALUES (@ProductOptionName);
            INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,SCOPE_IDENTITY())
END
      ELSE
BEGIN
            INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,[Found ProductOptionID here])
      END
// not the correct code but like this would work
just do an insert statement and select from your table, if it already exists it will not insert anything.


Insert into ProdcuctOptions(
field list
)
Select (ValueList)
FROM ProductionOptions
WHERE ProductID IS NULL
SOLUTION
Avatar of brad2575
brad2575
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Drevo

ASKER

Brad, won't your SQL insert duplicate ProductOptions? I'm looking for something that only inserts a new ProductOption record if there isn't already one with the same ProductOptionName.

Radcaesar and Edemcs, the problem with the code I included is that I'm not sure how to reference the [Found ProductOptionID here] part when the ProductOptionName is found to already exist.

Thanks!
Avatar of Drevo

ASKER

Here's what I am ending up with to make this work:
IF NOT EXISTS (SELECT ProductOptionID FROM ProductOptions WHERE ProductOptionName = @ProductOptionName)
BEGIN
    INSERT INTO ProductOptions (ProductOptionName ) VALUES (@ProductOptionName);
    INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,SCOPE_IDENTITY())
END
ELSE
BEGIN
    INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,
    (SELECT TOP 1 ProductOptionID FROM ProductOptions 
     WHERE ProductOptionName =@ProductOptionName
    ))
END

Open in new window


Though, if someone has a more optimized way to accomplish this, I'd love to see it!
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No my code shouldnt because you are selecting the data to insert only where it is NULL from the table

So if this select (selecting from the table returns nothing you will insert nothing)
Select (ValueList)
FROM ProductionOptions
WHERE ProductName = (ValueProdcutName)
AND ProductID IS NULL
 SELECT @NewID = SCOPE_IDENTITY