sql-server-2005

How to upsert (update or insert) in SQL Server 2005


I have table in which I am inserting rows for employee but next time when I want to insert row I don't want to insert again data for that employee just want to update with required columns if it exits there if not then create new row

How can we do this in SQL Server 2005?

I am using jsp

my query is

String sql="insert into table1(id,name,itemname,itemcatName,itemQty)values('val1','val2','val3','val4','val5')";

if it's first time then insert it into database else if exists update it

how to do?


Solution

  • Try to check for existence:

    IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID)
    
        INSERT INTO dbo.Employee(Col1, ..., ColN)
        VALUES(Val1, .., ValN)
    
    ELSE
    
        UPDATE dbo.Employee
        SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
        WHERE ID = @SomeID
    

    You could easily wrap this into a stored procedure and just call that stored procedure from the outside (e.g. from a programming language like C# or whatever you're using).

    Update: either you can just write this entire statement in one long string (doable - but not really very useful) - or you can wrap it into a stored procedure:

    CREATE PROCEDURE dbo.InsertOrUpdateEmployee
           @ID INT,
           @Name VARCHAR(50),
           @ItemName VARCHAR(50),  
           @ItemCatName VARCHAR(50),
           @ItemQty DECIMAL(15,2)
    AS BEGIN
        IF NOT EXISTS (SELECT * FROM dbo.Table1 WHERE ID = @ID)
           INSERT INTO dbo.Table1(ID, Name, ItemName, ItemCatName, ItemQty)
           VALUES(@ID, @Name, @ItemName, @ItemCatName, @ItemQty)
        ELSE
           UPDATE dbo.Table1
           SET Name = @Name,
               ItemName = @ItemName,
               ItemCatName = @ItemCatName,
               ItemQty = @ItemQty
           WHERE ID = @ID
    END
    

    and then just call that stored procedure from your ADO.NET code