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?
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