Converting MERGE into T-SQL commands

Question

I have got a T-SQL MERGE command that works fine on my SQL Server 2008. Unfortunately, the code has to work on a SQL Server 2005 database, too.

What would I have to change, to make the following code runnable in SQL Server 2005?

Thank you for your help.

DECLARE @CNVAL varchar(100) = 'xyz' DECLARE @ATRBT varchar(100) = 'abcde' DECLARE @CON varchar(100) = 'fgh' 
MERGE into CNEXTR as target
USING (VALUES( @CON, @ATRBT, @CNVAL))
     AS source([Name], ATTR, CNVAL)
     ON (target.[Name] = source.[Name])
     AND (target.ATTR = source.ATTR)
WHEN MATCHED THEN 
     UPDATE SET 
         CNVAL = source.CNVAL 
WHEN NOT MATCHED THEN 
     INSERT([Name], ATTR, CNVAL) 
     VALUES(source.[Name], source.ATTR, source.CNVAL);

gbn · Accepted Answer · 2012-05-22 11:49:12Z

5

Change this to

BEGIN TRAN

   UPDATE CNEXTR
   SET CNVAL = @CNVAL
   WHERE [Name] = @CON AND ATTR = @ATRBT;

   IF @@ROWCOUNT = 0
       INSERT CNEXTR ([Name], ATTR, CNVAL)
       VALUES(@CON, @ATRBT, @CNVAL);

COMMIT TRAN

Each statement will be far simpler than the MERGE

answered May 22, 2012 at 11:49

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting MERGE into T-SQL commands

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related