SQL Server 错误:列名或提供的值数量与表定义不匹配 [英] SQL Server error: Column name or number of supplied values does not match table definition

问题描述

为什么要查询:

INSERT INTO [IN4MATICSystem_Pie].[dbo].[ArchivioErogazioni] 
   SELECT 
       IDTER, CODTER, IDTEV, CODTEV,  IDVEI, CODVEI, IDTEA, CODTEA, IDAUT, CODAUT,  
       IDGRV, CODGRV, IDGRA, CODGRA, IDERO, CODERO, IDPRO, CODPRO, DATA_EROG, 
       ORA_EROG, KMATTUALI, VOLUME_LT, IMPORTO, PRUNIT, INFO, MODIFICATO,  
       ANOMALA, ACCETTATA, ESPORTATO, IMPORTATO, IMPORTRIF, USER_INS, DATA_INS, 
       USER_MOD, DATA_MOD, TRAN_ID, TEMPERATURA, KG, VOLUME_COMPENSATO, ORE 
   FROM 
       [IS-SERVER_SQL\SVILUPPO].IN4MATICSystem_ARC.dbo.ArchivioErogazioni 
   WHERE 
       DATA_EROG >= CONVERT(datetime,'30/11/2014 00:00:00',103) 
       AND DATA_EROG <= CONVERT(datetime,'01/01/2015 23:59:59',103)

抛出这个错误:

列名或提供的值数量与表定义不匹配.

Column name or number of supplied values does not match table definition.

这个查询不会抛出任何东西:

This query doesn't throw anything:

 SELECT IDTER, CODTER, IDTEV, CODTEV,  IDVEI, CODVEI, IDTEA, CODTEA, IDAUT, CODAUT,  IDGRV, CODGRV, IDGRA, CODGRA, IDERO, CODERO,
 IDPRO, CODPRO, DATA_EROG, ORA_EROG, KMATTUALI, VOLUME_LT, IMPORTO, PRUNIT, INFO, MODIFICATO,  ANOMALA, ACCETTATA, ESPORTATO, IMPORTATO, 
 IMPORTRIF, USER_INS, DATA_INS, USER_MOD,  DATA_MOD, TRAN_ID, TEMPERATURA, KG, VOLUME_COMPENSATO,  ORE 
FROM [IS-SERVER_SQL\SVILUPPO].IN4MATICSystem_ARC.dbo.ArchivioErogazioni WHERE DATA_EROG >= CONVERT(datetime,'30/11/2014 00:00:00',103) AND DATA_EROG <= CONVERT(datetime,'01/01/2015 23:59:59',103)

为什么?

推荐答案

例外情况清楚地表明 - 所选列的编号或值的顺序与 ArchivioErogazioni 中定义的列不匹配.

It is clearly said by exception - that columns number or order of values selected doesn't match columns defined in ArchivioErogazioni.

使用要插入的列的显式列表插入几乎总是更好.这也可以防止您出现许多逻辑"错误.

It is almost always better to insert using explicit list of columns to insert to. This can prevent you from many "logical" errors too.

例如，让我们假设表

dbo.SomeTable(Name_First nvarchar(20), Name_Last nvarchar(20))

假设您忘记了 SomeTable 中的列顺序是 Name_First 而不是 Name_Last 并且您像插入一样

Let's assume you just forget that column order in SomeTable is Name_First than Name_Last and you're inserting like

insert into dbo.SomeTable
select Name_Last, Name_First
from Some_Other_Table_Or_Subquery

在这种情况下，您不会收到任何错误 - 但显然您会插入错误的值(交换名称).

In this case you will get no errors - but obviously you will insert wrong values (swapped names).

考虑这种方法:

insert into dbo.SomeTable (Name_Last, Name_First)
select Name_Last, Name_First
from Some_Other_Table_Or_Subquery

在这种情况下，您不需要记住要插入的表中的列顺序 - 因为您明确指定了要插入的列和顺序 - 因此在这种情况下不太可能发生先前的逻辑错误.

In this case you don't need to remember columns order in the table you're inserting - as you explicitly specified columns you're inserting and order - so previous logical error is unlikely to happen in this case.

这是一个有点愚蠢"的例子，但我希望它是有道理的.

It's a little bit "silly" example, but I hope it makes sense.

这篇关于SQL Server 错误:列名或提供的值数量与表定义不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！