INSERT语句的select列表包含的项比INSERT列表要少

I'm trying to insert into a table variable, both from a View and from a Table, this is the code I'm attempting to use:

我试图从视图和表中插入一个表变量，这就是我要使用的代码:

DECLARE @ResultCodes TABLE(ID_Audit INT,
                           AcctCode VARCHAR(3),
                           BidYear VARCHAR(4),
                           Num_Htls INT,
                           AuditStatus VARCHAR(50),
                           Account VARCHAR(3),
                           X INT, E INT, H INT, H_NLRA INT, N INT, Extra INT)

INSERT INTO @ResultCodes(ID_Audit, AcctCode, BidYear, Num_Htls, AuditStatus, Account, 
                         X, E, H, H_NLRA, N, Extra)
    SELECT 
        ID_Audit,
        AcctCode,
        BidYear,
        COUNT(ID_RFP),
        Audit_Status,
        Account 
    FROM 
        dbo.c_Audit_Rec_Status 
    WHERE 
        ID_Status_Audit = 105
    GROUP BY ID_Audit,AcctCode,BidYear,Audit_Status,Account
        UNION
    SELECT 
        SUM(CASE WHEN ResultCode3 = 'X' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'E' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'H' AND Rate_Basis = 'LRA' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'H' AND Rate_Basis = 'NLRA' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'N' THEN 1 ELSE 0 END),
        0
    FROM 
        tbl_FA_Results WHERE AuditDate3 IS NOT NULL         

        SELECT * FROM @ResultCodes

But I keep getting this error

但是我一直得到这个错误

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

INSERT语句的选择列表包含的条目比插入列表少。选择值的数量必须与插入列的数量匹配。

I've looked at this a hundred times if I've looked at it once and it seems like it should be working, can anyone see what I'm missing?

我已经看了一百次了如果我看过一次，它看起来应该是有效的，有人能看到我遗漏了什么吗?

EDIT

编辑

This is the code I ended up with that works like a charm. Sometimes a second set of eyes is all it takes:

这是我最后用的代码，像一个符咒。有时只需要第二副眼睛:

DECLARE @ResultCodes TABLE(ID_Audit INT,AcctCode VARCHAR(3),BidYear VARCHAR(4),Num_Htls INT,AuditStatus VARCHAR(50),Account VARCHAR(3),X INT,E INT,H INT,H_NLRA INT,N INT,Extra INT)
INSERT INTO @ResultCodes(ID_Audit,AcctCode,BidYear,Num_Htls,AuditStatus,Account,X,E,H,H_NLRA,N,Extra)
    SELECT 
        ID_Audit,
        AcctCode,
        BidYear,
        COUNT(c_Audit_Rec_Status.ID_RFP),
        Audit_Status,
        Account ,
        SUM(CASE WHEN ResultCode3 = 'X' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'E' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'H' AND Rate_Basis = 'LRA' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'H' AND Rate_Basis = 'NLRA' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ResultCode3 = 'N' THEN 1 ELSE 0 END),
        0
    FROM 
        dbo.c_Audit_Rec_Status,
        tbl_FA_Results  
    WHERE 
        c_Audit_Rec_Status.ID_Status_Audit = 105 AND AuditDate3 IS NOT NULL
    GROUP BY ID_Audit,AcctCode,BidYear,Audit_Status,Account

1 个解决方案

#1

ID_Audit,AcctCode,BidYear,Num_Htls,AuditStatus,Account,X,E,H,H_NLRA,N,Extra

is like 15 things.

就像15。

  ID_Audit,
    AcctCode,
    BidYear,
    COUNT(ID_RFP),
    Audit_Status,
    Account

Is like 6 things. That won't work.

就像6。是行不通的。

For more info :https://www.google.com/search?q=The+select+list+for+the+INSERT+statement+contains+fewer+items+than+the+insert+list.&oq=The+select+list+for+the+INSERT+statement+contains+fewer+items+than+the+insert+list.&aqs=chrome..69i57j0j69i64l3.160j0j1&sourceid=chrome&es_sm=91&ie=UTF-8

更多信息:https://www.google.com/search?q=The +选择+列表+ + + +插入语句+有+ +项目+少于+ +插入+列表.&oq = +选择+ + + + +插入语句+有+ +项目+少于+ +插入+列表.&aqs = chrome . . 69 i57j0j69i64l3.160j0j1&sourceid = chrome&es_sm = 91 ie = utf - 8

#1

ID_Audit,AcctCode,BidYear,Num_Htls,AuditStatus,Account,X,E,H,H_NLRA,N,Extra