I have recently been asked if we can write a Merge T-sql to handle inserts and updates in the same statement for a Database on SQL Server 2008 at SQL 2000 compatibility level.
The answer is YES and NO; I have a SQL Server 2008 Instance on SP3 and the below illustration works on it. Tried that on SQL Server 2008 Instance on SP1 this fails with the below error
Msg 325, Level 15, State 1, Line 214
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.
You can use Merge statement to handle Inserts and updates for SQL 2000 compatible database.
Test it for yourself using the below script.
create database testmerge go alter database testmerge set compatibility_level = 80 go create table merge_test ( id int identity(1,1) primary key, name sysname, email varchar(250) ) go create procedure uspTestMerge @name sysname, @email varchar(250) as begin merge merge_test as target using (select @name,@email) as source (name,email) on (target.name= source.name) when matched then update set target.email = source.email when not matched then insert (name,email) values (source.name,source.email) output deleted.*, $action, inserted.* ; end go uspTestMerge 'Ivan','Ivan@test.co.uk' go uspTestMerge 'david','Ivan@test.co.uk' go uspTestMerge 'suleman','email@example.com' go select * from merge_test go uspTestMerge 'david','David@test.co.uk' go select * from merge_test go