Sql server Merge is a great tool as mentioned in my earlier blog. In this blog, i have a better example of using sql server 2008 merge.
If Not Exists(select * from sys.tables where name = 'TestTable')
Create Table TestTable(Id Int, SerialNo Int, Notes Varchar(50))
Declare @Tbl Table(SerialNo Int, Notes Varchar(50))
begin tran
Insert into TestTable(Id, SerialNo, Notes)
Values(1, 1, 'shouldnot be affected')
Insert into TestTable(Id, SerialNo, Notes)
Values(1, 2, 'shouldnot be affected')
Insert into TestTable(Id, SerialNo, Notes)
Values(2, 1, 'to-delete')
Insert into TestTable(Id, SerialNo, Notes)
Values(2, 2, 'to update')
Insert into @Tbl(SerialNo, Notes)
values(3, 'to insert')
Insert into @Tbl(SerialNo, Notes)
values(2, ' updated value')
select * from TestTable
Declare @Id Int = 2
Merge TestTable as Target
Using @Tbl as Source
On Target.Id = @Id And Target.SerialNo = Source.SerialNo
When Matched Then Update Set Target.Notes = Source.Notes
When Not Matched By Target Then Insert(Id, SerialNo, Notes)
Values(@Id, SerialNo, Notes)
When Not Matched By Source And Target.Id = @Id Then Delete
;
-- all items with id 1 must be present
select * from TestTable
rollback
