Search code examples
sql-servert-sqlinsertupsert

SQL Server Insert - Update Statements


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
        )

Solution

  • 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 );