Merge to Update / Insert using a single statement

My best guess is that you can use Merge to Update / Insert using a single statement

-- Merge Statement
MERGE ProjectDetailsTransit  AS spdt1
    USING (select d.ProjectDetailsTransitID,d.Startdate ,d.enddate from DeletedDayInclude d) AS ddi
    ON spdt1.ProjectDetailsTransitID = ddi.ProjectDetailsTransitID
    WHEN MATCHED THEN UPDATE SET spdt1.startDatetime =ddi.startDate, spdt1.FinishDateTime = ddi.enddate
    WHEN NOT MATCHED THEN INSERT(startDatetime ,FinishDateTime )    VALUES(ddi.startDate,ddi.enddate );
GO

Note : In insert statement of  Merge we can not use alias name with fields for ex:
INSERT(stm.User_Id ,stm.Favorite_question )
VALUES(sd.User_Id ,(cast(@questionid as varchar(50)))

if we will write it in the way it defined into blue marked code it will give us following error :
Error 1:  The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.

Because Query parse looks for column name in target table and it densest expect any other table name. So if we will write the alias table name with column name then it will try to find "stm.User_Id" name in columns of target table,but it wont find it there we will get above error message.

 Error 2:   Sometime we get following error if we try to execute newly build sp in which we have used Merge statement:
Msg 325, Level 15, State 1, Line 7
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

 Reason :  

Mostly with each new version of sql server we upgrade our data base and this is very true for application build several year back. In this case, the new functionality get issues to execute because still we are running database on compatibility mode of old version and it is not Compatible with new version of sqlserver for eg.
Compatibility mode is 90 for sqlserver 2005 
and Compatibility mode is 100 for sqlserver 2008

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

Happy Living...
Happy Coding...
Happy Concepts...