列名或编号...与创建脚本创建的表中的表定义不匹配.将行从一个表插入到另一个表时显示错误

我在不同的服务器实例中有两个相同的表.一台服务器用于生产，另一台用于测试.使用 SQL management studio 创建的脚本创建的测试表(右键单击表 --> 脚本表为 --> 创建).要移动测试数据，我使用链接服务器和以下代码:

set identity_insert ...在插入到<Server>.<DB>.<schema>.<TestTb>从<Server>.<DB>.<schema>.<ProdTB>中选择前100个*set identity_insert ...离开

以上适用于我创建的几个表.在最后一个中，我收到列名或提供的值的数量与创建脚本创建的表中的表定义不匹配"错误.我检查了列排序规则，一切正常.>

我唯一的区别是我没有创建在生产环境中找到的所有索引，但我不认为这会导致错误.

我正在使用 Sql server 2008.

解决方案

始终 在 insert 语句中指定列列表，并且在 insert...select 中必须始终指定两次 - 都在insert 子句和 select 子句.

此外，如果您使用 set identity_insert on 而没有在 insert 子句中明确指定列列表，SQL Server 将引发错误，因此即使您确实获得了所有列顺序正确，在这种情况下您仍然会收到错误.

有关更多信息，请阅读 Aaron Bertrand 的 要戒掉的坏习惯:没有列列表的 SELECT 或 INSERT，Shnugo 在他的评论中链接到.

I have two identical tables in different server instances. One server is production and the other one is for testing. The testing tables where created by using scripts created by SQL management studio (Right click on table -->script table as --> Create). To move test data i am using a linked server and the following code :

set identity_insert <Server>.<DB>.<schema>.<SomeID> ON
insert into <Server>.<DB>.<schema>.<TestTb>
select top 100 * from <Server>.<DB>.<schema>.<ProdTB>
set identity_insert <Server>.<DB>.<schema>.<SomeID> OFF

The above worked for a couple of the tables i created. In the last one, i get the "column name or number of supplied values does not match table definition in table created by create script" error.i have checked the Columns collation and everything is ok.

The only difference i have is that i haven't created all the indexes found in the Production env, but i don't really think this causes the error.

I' m working on Sql server 2008.

解决方案

Always specify the columns list in insert statements, and in insert...select you must always specify it twice - both in the insert clause and in the select clause.

Also, SQL Server will raise an error if you use set identity_insert on without explicitly specifying the columns list in the insert clause, so even if you did get all the columns in the correct order, you would still get an error in this case.

For more information, read Aaron Bertrand's Bad habits to kick: SELECT or INSERT without a column list which Shnugo linked to in his comment.