Once we have restored a database from SQL Server 2005 to SQL Server 2008 server, then we have executed a statement by using “Merge” as below:
MERGE Test_Merge as TM USING ( SELECT * FROM Test ) as T ON TM.EmpID = T.EmpID WHEN MATCHED THEN UPDATE SET TM.ENAME = T.ENAME, TM.DEPTNO = T.DEPTNO, TM.SAL = T.SAL, TM.DOB = T.DOB WHEN NOT MATCHED THEN INSERT ( ENAME , DEPTNO , SAL , DOB ) VALUES ( T.ENAME , T.DEPTNO , T.SAL , T.DOB )
when we use the above statement in the restored database, then we will be getting the below error:
<strong>Msg 325, Level 15, State 1, Line 67</strong>
Incorrect syntax near ‘MERGE’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
<strong>Msg 156, Level 15, State 1, Line 70</strong> <strong>Incorrect syntax near the keyword 'as'.
This error was due to the compatiablity level. As the compatiability level for the restored database in 90, first we need to update the compatiability level to 100, this can be done in the following way:
ALTER DATABASE [RestoredDatabase] SET COMPATIBILITY_LEVEL = 100 OR SP_DBCMPTLEVEL 'RestoredDatabase',100