Insert/Update command with Case in sql server

[Uma Kameswari]

I have to write a insert/update command with in a case. IS it possible? for example select case(select count(*) from table1 a where a.no=b.no and a.act=b.act) when 0 then insert into temp(name,desc,status) values(b.name,b.desc,1) when 1 then update temp set name=b.name,desc = b.desc,status=2 where id=b.id else insert into temp(name,desc,status) values(b.name,b.desc,3) end from table2 b But as case statement accepts only expressions can some one let me know how the above can be modified

[Wendelius]

You cannot combine insert and update statements, so you have to execute two separate commands. However you can execute them in a single batch (or write a procedure to wrap them).

The need to optimize rises from a bad design.My articles[^]