Replace Into in SQL Server 2008

Question

I try to do a migration but i have problem with this query :

$DB->query("replace into periodetojour(idperiode,idjour,heure)
            values('".addslashes($idperiode)."','2','".addslashes($mardi)."')");

I saw that REPLACE INTO can't be used in SQL Server 2008 and that i hav to use MERGE INTO

My problem is that i don't find any of my query which is working by using MERGE INTO so i am probably not using it well. Do you have any idea how can i change it with MERGE INTO and is it an obligation to change it in SQL Server 2008 ?

Thanks for your answer.

Answer 1

In your case, the MERGE statement would look like this:

$DB->query("MERGE INTO periodetojour dst 
            USING ( 
              SELECT '".addslashes($idperiode)."' idperiode, 
                     '2' idjour, 
                     '".addslashes($mardi)."' heure 
            ) src  
            ON src.idperiode = dst.idperiode  
            WHEN MATCHED THEN UPDATE SET  
              dst.idjour = src.idjour, 
              dst.heure = src.heure  
            WHEN NOT MATCHED THEN INSERT (idperiode, idjour, heure)  
              VALUES(src.idperiode, src.idjour, src.heure)");

This is assuming that idperiode is your primary key. If the primary key is composed of (idperiode, idjour) you'll have to adapt the ON clause as well as the WHEN MATCHED THEN UPDATE SET clause accordingly:

            -- [...]
            ON src.idperiode = dst.idperiode  
            AND src.idjour = dst.idjour
            WHEN MATCHED THEN UPDATE SET  
              dst.heure = src.heure
            -- [...]