I have the following code: the problem is, when I run the insert into, it would insert into the food table even the result are already inserted, how could I write an if statement that would check if the records in NEWfoods already exist in food table, if they exist in food, just don't do an insert, if they don't then do the insert. Thank you.
CREATE TABLE food (FoodName NVARCHAR(200), FoodType NVARCHAR(200));
CREATE TABLE NEWfoods (FoodName NVARCHAR(200), FoodType NVARCHAR(200));
-- populate base table (existing database table)
INSERT INTO food (FoodName, FoodType) VALUES
('Apples', 'Fruit')
,('Avocado','Fruit')
,('Bananas', 'Fruit')
,('Mangos', 'Fruit')
,('Bread', 'Grain')
,('Cottage Cheese', 'Dairy')
,('Tacos', 'Meals')
,('Carrots', 'Vegetables')
,('Celery', 'Vegatables')
-- populate NEW table of foods which we will use ti import into;
INSERT INTO NEWfoods ( FoodName, FoodType ) VALUES
('Avocado','Vegetables')
,('Apples','Fruit')
,('Salt','Preservative')
,('Turkey','Protein')
,('Bread','Grain')
,('Bread','Grain')
,('Tacos','Meals')
-- add in this list of foods if the pair does not exist;
-- this will become an INSERT INTO when said and done;
INSERT INTO food
SELECT
f.FoodName
,f.FoodType
FROM food AS f
WHERE NOT EXISTS (
SELECT * FROM NEWfoods AS g
where g.FoodName = f.FoodName
AND g.FoodType = f.FoodType
)