February 9, 2009 at 9:24 am
I need to Insert or update the records into a table based on a condition. Can I use case statement for something like this:
select case isnew
when 1 then ( insert into mytable ... )
else (update mytable set ...)
end
from test_table
February 9, 2009 at 9:37 am
You can't do this using CASE statement, you can to handle this using 2 DML statements....
INSERTmytable( SomeColumn )
SELECTSomeColumn
FROMtest_table
WHEREisnew = 1
UPDATEmt
SETmt.SomeColumn = tt.SomeColumn
FROMmytable mt
INNER JOIN test_table tt ON tt.isnew = 0 AND mt.SomeID = tt.SomeID
--Ramesh
February 9, 2009 at 9:43 am
Thanks. I wanted to confirm if CASE can be used for such a thing. It would make things easier 🙂
February 9, 2009 at 3:06 pm
[font="Verdana"]Check out the merge statement in SQL Server 2008.[/font]
February 10, 2009 at 6:16 am
gyessql (2/9/2009)
I need to Insert or update the records into a table based on a condition. Can I use case statement for something like this:
select case isnew
when 1 then ( insert into mytable ... )
else (update mytable set ...)
end
from test_table
use functions or stored porcedures
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
February 10, 2009 at 10:17 am
Can I use dynamic sqls. Will there be a performance difference if I have more dynamic sqls in my stored proc?
February 10, 2009 at 10:32 am
This is not possible with CASE, but if you define functions to insert or update then you can call these in CASE expressions.
The most simple thing is using MERGE. this very good for conditional DMLs.
Thanks
Vijaya Kadiyala
February 10, 2009 at 11:02 am
Is MERGE supported in Sql Server 2005?
February 10, 2009 at 1:24 pm
No its not. I think its a new feature in SQL 2008
February 10, 2009 at 1:34 pm
[font="Verdana"]It's absolutely a new feature in SQL Server 2008. It's certainly not available in SQL Server 2005.[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply