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
)
I can think of a couple of ways if you are using SQL Server 2008 or later:
Insert Food ( FoodName, FoodType )
Select FoodName, FoodType
From NewFood
Except
Select FoodName, FoodType
From Food
Another is to use the Merge statement:
Merge Food As target
Using NewFoods As source
On source.FoodName = target.FoodName
And source.FoodType = target.FoodType
When Not Matched Then
Insert ( FoodName, FoodType )
Values( source.FoodName, source.FoodType );