在MSSQL中使用INSERT INSERT CASE语句

Question

我使用的是MSSQL。下面是我的sql代码

DECLARE @CoursesAboutToExpire TABLE (CourseID   BIGINT, ExpiryDate  DATE, IsApplicableToAllInternalUser BIT)

INSERT INTO @CoursesAboutToExpire
    SELECT CourseID, ExpiryDate, IsApplicableToAllInternalUser
    FROM CMS_CoursesMaster CM

DECLARE @ApplicableUsersWithCourse TABLE (UserID    BIGINT, CourseID    BIGINT)


SELECT 1 ,
    (CASE WHEN CAE.IsApplicableToAllInternalUser = 1
        THEN (INSERT INTO @ApplicableUsersWithCourse SELECT UM.UserID, CAE.CourseID FROM TRC_UserMaster UM)
        ELSE (INSERT INTO @ApplicableUsersWithCourse SELECT CAP.UserID, CAP.CourseID FROM CMS_CourseApplicabilityParameters CAP WHERE CAP.CourseID=CAE.CourseID)
    ) AS 2
FROM @CoursesAboutToExpire CAE

我尝试使用case语句在*IsApplicableToAllInternalUser *列值的基础上插入记录。但它会产生错误。

Answer 1

现在，您只需将尝试执行的CASE语句替换为以下INSERT INTO .. SELECT语句：

DECLARE @ApplicableUsersWithCourse TABLE (UserID    BIGINT, CourseID    BIGINT)

INSERT INTO @ApplicableUsersWithCourse 
SELECT UM.UserID, CAE.CourseID 
FROM TRC_UserMaster UM JOIN @CoursesAboutToExpire CAE ON UM.CourseID = CAE.CourseID 
WHERE CAE.IsApplicableToAllInternalUser = 1

INSERT INTO @ApplicableUsersWithCourse 
SELECT CAP.UserID, CAP.CourseID 
FROM CMS_CourseApplicabilityParameters CAP JOIN @CoursesAboutToExpire CAE ON CAP.CourseID=CAE.CourseID
WHERE CAE.IsApplicableToAllInternalUser = 0

只需先执行SELECT语句，就可以对要插入的记录进行初始测试。如果看起来没问题，您可以插入记录。

这也可以写成如下：

INSERT INTO @ApplicableUsersWithCourse 

SELECT UM.UserID, CAE.CourseID 
FROM TRC_UserMaster UM JOIN @CoursesAboutToExpire CAE ON UM.CourseID = CAE.CourseID 
WHERE CAE.IsApplicableToAllInternalUser = 1

UNION ALL

SELECT CAP.UserID, CAP.CourseID 
FROM CMS_CourseApplicabilityParameters CAP JOIN @CoursesAboutToExpire CAE ON CAP.CourseID=CAE.CourseID
WHERE CAE.IsApplicableToAllInternalUser = 0

Answer 2

下面的代码对我很有效。

 DECLARE @CoursesAboutToExpire TABLE (CourseID   BIGINT, ExpiryDate  DATE, IsApplicableToAllInternalUser BIT)

INSERT INTO @CoursesAboutToExpire SELECT CourseID, ExpiryDate, IsApplicableToAllInternalUser FROM CMS_CoursesMaster CM

DECLARE @ApplicableUsersWithCourse TABLE (UserID BIGINT, CourseID BIGINT)

INSERT INTO @ApplicableUsersWithCourse SELECT UM.UserID, CAE.CourseID FROM TRC_UserMaster UM CROSS JOIN @CoursesAboutToExpire CAE WHERE CAE.IsApplicableToAllInternalUser = 1)

INSERT INTO @ApplicableUsersWithCourse SELECT CAP.UserID, CAP.CourseID FROM CMS_CourseApplicabilityParameters CAP INNER JOIN @CoursesAboutToExpire CAE ON CAP.CourseID=CAE.CourseID WHERE CAE.IsApplicableToAllInternalUser = 0