CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2
Vipul Kumar
Vipul Kumar

Reputation: 416

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

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'.**

Upvotes: 0

Views: 260

Answers (1)

Martin Smith
Martin Smith

Reputation: 453288

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.

Upvotes: 1

Related Questions