列名或提供的值的数量与 sql 服务器中的表定义不匹配

Question

>我有以下SQL Server游标代码段来插入数据

-- declare a cursor 
DECLARE insertapiinvoicedetail_cursor CURSOR FOR
SELECT * FROM Account_APOrderDetail WHERE APOD_Master_Id = @var2;
-- open cursor and fetch first row into variables
OPEN insertapiinvoicedetail_cursor
FETCH NEXT FROM insertapiinvoicedetail_cursor INTO
@Ref_Code,
@Create_UserId,
@Create_Date,
@Modification_UserId,
@Modification_Date,
@CompanyId  
-- check for a new row
WHILE @@FETCH_STATUS=0
BEGIN
-- do complex operation here
INSERT INTO Account_APInvoiceDetail
SELECT @Ref_Code,
@Create_UserId,
@Create_Date,
@Modification_UserId,
@Modification_Date,
@CompanyId
-- get next available row into variables
FETCH NEXT FROM insertapiinvoicedetail_cursor INTO @Ref_Code,
@Create_UserId,
@Create_Date,
@Modification_UserId,
@Modification_Date,
@CompanyId
END
close insertapiinvoicedetail_cursor
Deallocate insertapiinvoicedetail_cursor
GO

但是我在这里收到以下错误消息

'Msg 213，级别 16，状态 1，第 128
行 列名或提供的值数与表定义不匹配。

如何解决此问题？

Answer 1

您收到此错误是因为Account_APInvoiceDetail表中的列数与 select 语句不匹配。

您应该在插入记录时指定列名，如下所示，它将起作用。

INSERT INTO Account_APInvoiceDetail
(Ref_CodeColumnName, Create_UserIdColumnName, Create_DateColumnName, Modification_UserIdColumnName, Modification_DateColumnName, CompanyIdColumnName)
SELECT @Ref_Code,
@Create_UserId,
@Create_Date,
@Modification_UserId,
@Modification_Date,
@CompanyId
-- get next available row into variables
FETCH NEXT FROM insertapiinvoicedetail_cursor INTO @Ref_Code,
@Create_UserId,
@Create_Date,
@Modification_UserId,
@Modification_Date,
@CompanyId

注意：提供实际列名称，而不是Ref_CodeColumnName、Create_UserIdColumnName、Create_DateColumnName、Modification_UserIdColumnName、Modification_DateColumnName、公司 Id 列名称