Merge statement in Sql server with target table as existing table in Sql Server 2008

Question

QUESTION:I have table ARMS.RefRollno in my database and with roll no and rank now i have query which is returning a dataset of roll and rank .If my table contains that row then i need to update it and if not that i want to insert a new row in respect to that roll no.

    create table #temp
    (
      ROLLNO varchar(100),
      Ranking varchar(100),
      TestRecID varchar(100)
    )
    INSERT INTO #temp (ROLLNO, Ranking,TestRecID) EXEC [ARMS].[GetStudentResultForUpdateRank] '412'

   MERGE ARMS.RefRollno AS C
    USING #temp AS CT
    ON C.TestRecID = CT.TestRecID
    WHEN MATCHED THEN
    UPDATE SET
    C.RefRank = CT.Ranking      
    WHEN NOT MATCHED THEN 
    INSERT (TestRecId,RefRollNo, RefRank,IsActive,CreatedDate)
    VALUES (CT.TestRecID,CT.ROLLNO,CT.Ranking, 1,getdate());
    drop table #temp

Here ARMS.RefRollno is my existing table in Database. Any Help is appreciated.

    ** Error by Sql Server:Incorrect syntax near 'MERGE'.**

Sql server is not allowing me to use target table as existing one. — Vipul Kumar
– Vipul Kumar, Commented Mar 5, 2014 at 8:33
@user1598415 - no, you've misdiagnosed the issue. A merge always targets an existing table. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 5, 2014 at 8:35
Please provide full code that reproduces the problem. Works fine for me. SQL Fiddle. Also check that you are definitely on 2008 not a previous version with SELECT @@VERSION — Martin Smith
– Martin Smith, Commented Mar 5, 2014 at 8:40

Martin Smith · Accepted Answer · 2014-03-05 09:24:22Z

1

Change

EXEC [ARMS].[GetStudentResultForUpdateRank] '412'

To

EXEC [ARMS].[GetStudentResultForUpdateRank] '412';

(Note addition of trailing semicolon).

This only seems to be necessary if the database is in an earlier compatibility mode than 2008.

answered Mar 5, 2014 at 9:24

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Collectives™ on Stack Overflow

Merge statement in Sql server with target table as existing table in Sql Server 2008

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