Drevo
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.
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
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(Produ ctID,Produ ctOptionID ) VALUES(@ProductID,SCOPE_ID ENTITY())
END
ELSE
BEGIN
INSERT INTO ProductOptionMapping(Produ ctID,Produ ctOptionID ) VALUES(@ProductID,[Found ProductOptionID here])
END
IF NOT EXISTS (SELECT ProductOptionID FROM ProductOptions WHERE ProductOptionName = @ProductOptionName)
BEGIN
INSERT INTO ProductOptions (ProductOptionName ) VALUES (@ProductOptionName);
INSERT INTO ProductOptionMapping(Produ
END
ELSE
BEGIN
INSERT INTO ProductOptionMapping(Produ
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
Here's what I am ending up with to make this work:
Though, if someone has a more optimized way to accomplish this, I'd love to see it!
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
Though, if someone has a more optimized way to accomplish this, I'd love to see it!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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