• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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

0
Drevo
Asked:
Drevo
2 Solutions
 
radcaesarCommented:
did u write this a trigger when updating/inserting? It should be, right?
0
 
edemcsCommented:
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
0
 
brad2575Commented:
// 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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
brad2575Commented:
Sorry left off second table:

Insert into ProdcuctOptions(
field list
)
Select (ValueList)
FROM ProductionOptions
WHERE ProductID IS NULL
 SELECT @NewID = SCOPE_IDENTITY

IF(@NEWID IS NOT NULL)
begin
  inert into other table
end
0
 
DrevoAuthor Commented:
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!
0
 
DrevoAuthor Commented:
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!
0
 
Anthony PerkinsCommented:
Something like this perhaps:
DECLARE @ProductOptionID integer

SELECT	@ProductOptionID = ProductOptionID
FROM	ProductOptions 
WHERE	ProductOptionName = @ProductOptionName

IF @ProductOptionID IS NULL
BEGIN
    INSERT INTO ProductOptions (ProductOptionName ) VALUES (@ProductOptionName);
    SET @ProductOptionID = SCOPE_IDENTITY()
END

INSERT INTO ProductOptionMapping(ProductID, ProductOptionID) VALUES(@ProductID, @ProductOptionID)

Open in new window

0
 
brad2575Commented:
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
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now