SQL insert into not exists on multiple results

Question

I have Plan table that has a one to many relationship with a Price table. (1 plan can have many prices)

However the problem is I have another table called "StandardPrices" that holds the names of all the prices (The reason for this is I want to be able to add or remove prices at any time)

 Plan Table:
 ID int primary key,
 plan Name varchar(200),
 ...

 PriceTable:
 ID int primary key,
 PlanId int foreign key references plan(ID)
 PriceName ID foreign key standardprices(id)

 StandardPrices:
 ID int primary key,
 PriceName varchar(200),
 DefaultPrice money

So whenever a plan is created, it automatically creates a List of all the prices in the StandardPrice list (with default values).

The problem I have, Is I need, whenever I create a new StandardPrice, it automatically checks if that price exists in every plan, and if it doesnt, create an entry in the price table for that planid.

I use Stored procedures and thought the best way to do this would be through SQL.

When StandardPrices are created:

   begin      
   insert into StandardPrices (PriceName, Defaultprice)
       values (@priceName, @DefaultPrice)
   end

   begin
   //list all plans.
   //cross reference PriceTable to see if plan exists
   //if not insert priceplan with default value
   end

I am a bit confused how i can implement such a sql command?

Answer 1

I think it looks something like this:

insert into PriceTable (PlanId, PriceName)
select PlanId, @priceName
from Plan
where not exists 
  (select null from PriceTable where PriceTable.PlanId = Plan.PlanId)

And you should probably do this as part of an INSERT trigger on your database.

Answer 2

Do something like this:

if not exists
(
    select *
    from PriceTable
    where PriceName = @priceName
)
begin
    insert into PriceTable(PriceName)
    values(@priceName)
end

What this does is it conditionally checks to see if that PriceName is already in the PriceTable. If it is not, then it will insert that new @priceName into the PriceTable. I wasn't sure by your original post what the value would be for PlanID, but you should get the idea from my above query.

Answer 3

(MySql) I'll suggest you to create with temporary table; It is clean and readable (I will add without alias to be more readable):

CREATE TEMPORARY TABLE tempTable
(
    table1FK    varchar(250),
    table2Value varchar(250)
);

INSERT INTO tempTable(table1FK, table2Value)
VALUES ('it', '+39'),
       ('lt', '+370'),
       ('gb', '+44'),
       ('cn', '+86');

INSERT INTO table2(table1FK_id, table2Value)
SELECT table1.id, tempTable.prefix
FROM tempTable
         inner join table1 ON table1.code = tempTable.country_code
         left join table2 ON table2.country_id = table1.id
where table2.id is null;

DROP temporary TABLE tempTable;

Answer 4

You need to get a list of all plans, LEFT OUTER joined to the appropriate price. If the price record is null, there isn't an appropriate association so add it.

You'll probably need to use a cursor to iterate through the results and add prices.

Something like..

SELECT Plan.Id, Price.Id
FROM Plan LEFT OUTER JOIN Price ON Price.PlanId = Plan.Id
WHERE Price.Id = [Your New Price Id]

Answer 5

Using MERGE and assuming your ID columns have the IDENTITY property:

MERGE INTO PriceTable
   USING 
      (
        SELECT p1.ID AS PlanId, sp1.PriceName 
         FROM Plan p1 
            CROSS JOIN StandardPrices sp1
      ) AS SourceTable
   ON PriceTable.PlanId = SourceTable.PlanId 
      AND PriceTable.PriceName = SourceTable.PriceName 
   WHEN NOT MATCHED 
      THEN
   INSERT ( PlanId, PriceName )
            VALUES ( PlanId, PriceName ) ;

Here is a fuller sketch, where I've drooped the seemingly redundant ID columns, promoted the name columns to relational keys, added constraints, used consistent naming between tables, changed MONET to DECIMAL, etc:

CREATE TABLE Plans
(
  plan_name VARCHAR(200) NOT NULL
                         UNIQUE
                         CHECK ( plan_name <> ' ' )
) ;

CREATE TABLE StandardPrices
(
  price_name VARCHAR(200) NOT NULL
                          UNIQUE
                          CHECK ( price_name <> ' ' ),
  default_price DECIMAL(19, 4) NOT NULL
                               CHECK ( default_price >= 0 )
) ;

CREATE TABLE Prices
(
  plan_name VARCHAR(200) NOT NULL
                         REFERENCES Plans ( plan_name ),
  price_name VARCHAR(200) NOT NULL
                          REFERENCES StandardPrices ( price_name ),
  UNIQUE ( plan_name, price_name )
) ;

DECLARE @plan_name_new VARCHAR(200) ;
DECLARE @price_name_1 VARCHAR(200) ;
DECLARE @default_price_1 DECIMAL(19, 4) ;
DECLARE @price_name_2 VARCHAR(200) ;
DECLARE @default_price_2 DECIMAL(19, 4) ;

SET @plan_name_new = 'One'
SET @price_name_1 = 'Day'
SET @default_price_1 = 55 ;
SET @price_name_2 = 'When'
SET @default_price_2 = 99

INSERT INTO Plans ( plan_name )
   VALUES ( @plan_name_new ) ;

INSERT INTO StandardPrices ( price_name, default_price )
   VALUES ( @price_name_1, @default_price_1 ) ;

INSERT INTO StandardPrices ( price_name, default_price )
   VALUES ( @price_name_2, @default_price_2 ) ;

MERGE INTO Prices
   USING 
      (
        SELECT p1.plan_name, sp1.price_name
         FROM Plans p1 
            CROSS JOIN StandardPrices sp1
      ) AS SourceTable
   ON Prices.plan_name = SourceTable.plan_name
      AND Prices.price_name = SourceTable.price_name
   WHEN NOT MATCHED 
      THEN
   INSERT ( plan_name, price_name )
            VALUES ( plan_name, price_name ) ;