CODEWITHSUNDEEP
sqlsql-server-2008t-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

UPDATE and INSERT in other table with MERGE when matched statement?

I googled this topic for some type of help/answers, but I didn't find much. I'm asking because I prefer MERGE; if not possible, then I'll go back to the regular Updates/Inserts.

I have two tables: [Course] and [CourseHistory]. Both have three columns: EmpId, CourseId, CourseName. The following MERGE statement works correctly in the sense that it UPDATEs or INSERTs to [Course] when it has to.

What I need to include is to insert into [CourseHistory] when there's an UPDATE. Essentially, I will be backing up the record that will be about to be modified.

I tried including a sort of INSERT statement, but I get syntax errors.

My tsql statement looks like the following:

merge dbo.Courses as pre              
using (select @EmpId as EmpId, @CourseId as CourseId) as S              
    on  pre.EmpId = S.EmpId and pre.CourseId = s.CourseId
  when matched then
   /* 
     INSERT INTO [CourseHistory] (EmpId, CourseId, CourseName)
     SELECT EmpId, CourseId, CourseName 
     where EmpId = @EmpId and @CourseId = CourseId
   */
    update set
        CourseName = @CourseName
  when not matched then              
    INSERT (CourseId, EmpId, CourseName)
    VALUES (@CourseId, @EmpId, @CourseName);

Upvotes: 2

Views: 3986

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Note: I've updated my answer based on rbhatup bellow comment.

I would use OUTPUT ... INTO ... clause with $action virtual column thus:

-- It create a temp. table with the same columns and data type like source table
SELECT TOP(0) *, CONVERT(NVARCHAR(10), N'') AS MergeAction 
INTO #AffectedRows 
FROM dbo.Courses;
-- If source table have one column with IDENTOTY property then this SELECT INTO will add also the IDENTITY property to the same column

MERGE... your statement ...
OUTPUT deleted.*, $action into  #AffectedRows; -- deleted = old values, inserted = new values

INSERT INTO [CourseHistory] -- You could add a new column to CourseHistory named [Action] (will store values from MergeAction)
SELECT * FROM #AffectedRows r
WHERE r.MergeAction = 'UPDATE'

Upvotes: 3

Related Questions