“合并"有文字值的样式操作? [英] "Merge" style operation with literal values?

问题描述

我有一个包含学生等级关系的表:

I have a table containing a student-grade relationship:

Student   Grade   StartDate   EndDate
   1        1    09/01/2009    NULL
   2        2    09/01/2010    NULL
   2        1    09/01/2009   06/15/2010

我正在尝试编写一个使用Student，Grade和StartDate的存储过程，我希望它成为

I am trying to write a stored procedure that takes Student, Grade, and StartDate, and I would like it to

1. 检查以确保这些值不是重复的
2. 如果记录不是重复的，则插入记录
3. 如果有一个现有的学生记录，并且它有一个EndDate = NULL，则使用新记录的StartDate更新该记录.

1. check to make sure these values are not duplicates
2. insert the record if it's not a duplicate
3. if there is an existing student record, and it has a EndDate = NULL, then update that record with the StartDate of the new record.

例如，如果我调用该过程并传递1，2，09/01/2010，则我最终会得到:

For instance, if I call the procedure and pass in 1, 2, 09/01/2010, I'd like to end up with:

Student   Grade   StartDate   EndDate
   1        2    09/01/2010    NULL
   1        1    09/01/2009   09/01/2010
   2        2    09/01/2010    NULL
   2        1    09/01/2009   06/15/2010

这听起来像我可以使用MERGE，只是我要传递文字值，并且我需要执行多个操作.今天早上我也感到头疼，似乎无法清晰地思考，因此我将重点放在此MERGE解决方案上.如果有更明显的方法可以做到这一点，请不要害怕指出这一点.

This sounds like I could use MERGE, except that I am passing literal values, and I need to perform more than one action. I also have a wicked headache this morning and can't seem to think clearly, so I am fixating on this MERGE solution. If there is a more more obvious way to do this, don't be afraid to point it out.

推荐答案

即使您传递文字值，也可以使用MERGE.这是您的问题的示例:

You can use a MERGE even if you are passing literal values. Here's an example for your issue:

CREATE PROCEDURE InsertStudentGrade(@Student INT, @Grade INT, @StartDate DATE)
AS
BEGIN;

    MERGE StudentGrade AS tbl
     USING (SELECT @Student AS Student, @Grade AS Grade, @StartDate AS StartDate) AS row
     ON tbl.Student = Row.Student AND tbl.Grade = row.Grade
    WHEN NOT MATCHED THEN
      INSERT(Student, Grade, StartDate)
       VALUES(row.Student, row.Grade, row.StartDate)
    WHEN MATCHED AND tbl.EndDate IS NULL AND tbl.StartDate != row.StartDate THEN
      UPDATE SET
        tbl.StartDate = row.StartDate;

END;

这篇关于“合并"有文字值的样式操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！