TSQL 帮助 |INSERT 语句的选择列表包含的项目少于插入列表 [英] TSQL Help | The select list for the INSERT statement contains fewer items than the insert list

问题描述

请看下面我的查询，我已经多次计算了列数，也检查了逗号，它们似乎没问题，但我不断收到错误

Please see my query below, I have counted the columns a number of times and also I have checked the commas, they seem to be fine but I keep on getting the error

INSERT 语句的选择列表包含的项目少于插入列表.SELECT 值的数量必须与 INSERT 列的数量相匹配.

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.

DECLARE @StatusValues TABLE (StatusId INT,StatusText VARCHAR(50))

INSERT INTO @StatusValues VALUES(1,'Code 1 - Entered E&D Waiver');
INSERT INTO @StatusValues VALUES(2,'Code 2 - Nursing Facility');
INSERT INTO @StatusValues VALUES(3,'Code 3 - Entered Assisted Living Waiver');
INSERT INTO @StatusValues VALUES(4,'Code 4 - Entered TBI/SCI Waiver');
INSERT INTO @StatusValues VALUES(5,'Code 5 - Entered IL Waiver');
INSERT INTO @StatusValues VALUES(6,'Code 6 - DECEASED');
INSERT INTO @StatusValues VALUES(7,'Code 7 - Refused Waiver Services');
INSERT INTO @StatusValues VALUES(8,'Code 8 - Other (Explain)');
INSERT INTO @StatusValues VALUES(NULL,'Please select');


INSERT INTO SWMPDD.dbo.Clients(
    AdditionalPertinent,
    Address1,
    Address2,
    ApplicationApprovedBy,
    ByWhom,
    City,
    ClientAt,
    ContactPerson,
    ContactPhone,
    County,
    DateClientContacted,
    Diagnostic,
    Diet,
    Direction,
    DateOfBirth,
    Email,
    FirstName,
    Gender,
    InOtherCase,
    InTakeDate,
    IPAddress,
    LastName,
    LastUpdateUser,
    LastUpdateTime,
    LockinStatus,
    Medicaid,
    Medicare,
    MethodofContact,
    MiddleInitial,
    OfficalComments,
    OtherComments,
    ParticipantId,
    ParticipantSignature,
    PersonResidenceCode,
    Phone,
    Physician,
    PhysicianAddress,
    PhysicianCity,
    PhysicianPhone,
    PhysicianZip,
    CreationDate,
    ReferralPhone,
    ReferralSoruce,
    RelationshipToClient,
    SignatureDate,
    SSN,
    [State],
    CreationUser,
    VerificationDate,
    VerificationOfMedicaidStatus,
    Zip,
    ClientId,
    StatusId,
    StatusText
)
SELECT 
    AdditionalPertinent,
    Address1,
    Address2,
    ApplicationApprovedBy,
    ByWhom,
    City,
    ClientAt,
    ContactPerson,
    ContactPhone,
    county,
    DateClientContacted,
    Diagnostic,
    Diet,
    Direction,
    DOB,
    EmailAddress,
    FirstName,
    Gender,
    InOtherCase,
    InTakeDate,
    ipaddress,
    LastName,
    lastUpdatedBy,
    lastupdatedDate,
    LockinStatus,
    Medicaid,
    Medicare,
    MethodofContact,
    MiddleInit,
    OfficalComments,
    OtherComments,
    ParticipantId,
    ParticipantSignature,
    PersonResidenceCode,
    phone,
    Physician,
    PhysicianAddress,
    PhysicianCity,
    PhysicianPhone
    PhysicianZip,
    recDate,
    ReferralPhone,
    ReferralSoruce,
    RelationshiptoClient,
    SignatureDate,
    SSN,
    [state],
    userid,
    VerificationDate,
    VerificationofMedicaidStatus,
    zip,
    NEWID(),
    (SELECT StatusId FROM @StatusValues WHERE StatusText = ReasonforRemovalCode) abc,
    Code8Other
FROM msdepart.dbo.tblParticipant;

推荐答案

您在 SELECT 部分和 INSERT (..column list..) 中的字段列表因此处缺少逗号而减 1:

Your list of fields in the SELECT portion and INSERT (..column list..) is out by 1 because of a missing comma here:

PhysicianPhone
PhysicianZip,

在选择中.它将 Phone 别名为 Zip，其他一切都不合时宜.

In the SELECT. It is aliasing Phone as Zip, and everything else falls out of step.

这篇关于TSQL 帮助 |INSERT 语句的选择列表包含的项目少于插入列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！