Merge Statement for a SQL Server 2000 compatible Database

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','suleman@test.co.uk'
go

select * from merge_test
go

uspTestMerge 'david','David@test.co.uk'
go

select * from merge_test
go
This entry was posted in SQL Server, T-SQL Scripts and tagged , , , , , , . Bookmark the permalink.

3 Responses to Merge Statement for a SQL Server 2000 compatible Database

  1. Pingback: Current Events

  2. Pingback: maillot pays bas 2012

  3. Pingback: Anonymous

Leave a Reply